Query database for List of tables & other details

imsuneeta · January 18th, 2005, 02:15 AM

hi techies..

I want to get a generalised sql query.. for any database.. that will list me..all the table names and their fields,datatype,length, nullable/not at one go.. in SQL 2000 Server..

I have worked out the following query..

"Select a.name as tablename, b.name as fieldname, b.length
from sysobjects a,
syscolumns b where a.xtype ='U' and a.id = b.id order by a.name"

but.. the result is not completely satisfying..

pls..support..
suneeta

jemacc · January 18th, 2005, 07:41 AM

try this

Code:

SELECT 
isc.table_name, 
isc.column_name, 
isc.data_type, 
COALESCE(isc.character_maximum_length, isc.numeric_precision),
si.rowcnt
FROM information_schema.columns isc
INNER JOIN information_schema.tables ist
ON isc.table_name = ist.table_name
INNER JOIN sysindexes si
ON isc.table_name = OBJECT_NAME(si.id)
WHERE ist.table_type = 'base table'
AND ist.table_name not like 'dt%'
AND si.indid IN (0,1)
ORDER BY isc.table_name, isc.column_name

Jaime E. Maccou

jemacc · January 18th, 2005, 07:48 AM

or you can also do it this way. add or delete columns as you see fit. know you the tables where to get the data.

Code:

SELECT isc.table_name, isc.column_name, isc.data_type
FROM   information_schema.columns isc
       INNER JOIN information_schema.tables ist
            ON isc.table_name = ist.table_name
WHERE  OBJECTPROPERTY(OBJECT_ID(ist.table_name), N'IsUserTable') = 1 
AND    OBJECTPROPERTY(OBJECT_ID(ist.table_name), N'IsMSShipped') = 0
AND    DATA_TYPE NOT IN ('text', 'ntext', 'image')
ORDER BY isc.table_name, isc.column_name

Jaime E. Maccou