Fred Hooper
fahooper at trapo.com
Thu Dec 6 15:54:36 CST 2007
Hi John,
I use the code below to get Describe-type information from SQL Server. It
gets it for the whole database but you can limit it, of course. There's
separate code for 2000 and earlier versus 2005; both are below.
Fred
For SQL Server 2000 and earlier I use:
SELECT distinct M.owner,
M.table_name,
M.column_name,
M.colorder,
M.field_type
+ case when M.field_type in
('int','bigint','tinyint','datetime','money','bit','smallint','smallmoney')
then ''
Else '(' + convert(varchar,case when M.field_type in
('nvarchar','varchar','char') then
case when M.length > 255 then
255 else M.length end
else M.length end) + ')' end as
data_type,
M.isnullable as nullable,
IsNull(K.key_fld,0) as primary_key,
IsNull(I.keyno, 0) As Indexed,
case when M.xtype = 'U' then 'T' else 'V' end as TableOrView
FROM /* Basic information on the tables and fields */
(select user_name(O.uid) as owner, O.name as table_name, C.name as
column_name,
C.colorder, T.name as field_type, C.length, C.isnullable,
O.xtype
from sysobjects O
inner join syscolumns C on O.id = C.id
inner join systypes T on C.xtype = T.xtype
where (O.xtype = 'U' or O.xtype = 'V')
and T.name <> 'sysname'
and user_name(O.uid) = 'dbo') M
/* Returns all of the key fields */
left outer join (select SO.name as table_name,
SC.name as column_name,
SIK.keyno AS key_fld
from sysobjects SO,
sysobjects SPK,
sysindexes SI,
sysindexkeys SIK,
syscolumns SC
where SO.xtype = 'U'
and SO.id = SPK.parent_obj
and SPK.xtype = 'PK'
and SO.id = SI.id
and SPK.name = SI.name
and SO.id = SIK.id
and SI.indid = SIK.indid
and SO.id = SC.id
and SIK.colid = SC.colid) K
on M.table_name = K.table_name and M.column_name = K.column_name
/* Returns all of the indexed fields */
left outer join (select object_name(si.id) as table_name,
col_name(sic.id, sic.colid) as column_name,
min(sic.keyno) as keyno
from sysindexes si,
sysindexkeys sic
where si.id = sic.id
and si.indid = sic.indid
and left(si.name,8) <> '_WA_Sys_' /* Omit statistics
indexes */
group by object_name(si.id), col_name(sic.id,
sic.colid)) I
on M.table_name = I.table_name and M.column_name = I.column_name
For SQL Server 2005 I use:
SELECT distinct M.owner,
M.table_name,
M.column_name,
M.colorder,
M.field_type
+ case when M.field_type in
('int','bigint','tinyint','datetime','smalldatetime',
'money','bit','smallint','smallmoney')
then ''
Else '(' + convert(varchar,case when M.field_type in
('nvarchar','varchar','char') then
case when M.length > 255 then
255 else M.length end
else M.length end) + ')' end as
data_type,
M.isnullable as nullable,
IsNull(K.key_fld,0) as primary_key,
IsNull(I.keyno, 0) As Indexed,
case when M.xtype = 'U' then 'T' else 'V' end as TableOrView
FROM /* Basic information on the tables and fields */
(select s.name as owner, o.name as table_name, c.name as column_name,
column_id as colorder, t.name as field_type, c.max_length as
length,
c.is_nullable as isnullable,
case when type_desc = 'USER_TABLE' then 'U' else 'V' end as
xtype
from sys.objects o
inner join sys.schemas s on o.schema_id = s.schema_id
inner join sys.columns c on o.object_id = c.object_id
inner join sys.types t on c.system_type_id =
t.system_type_id
where type_desc in ('USER_TABLE','VIEW')
and t.name <> 'sysname'
and s.name = 'dbo') M
/* Returns all of the key fields */
left outer join (select distinct o.name as table_name, c.name as
column_name, i.index_column_id as key_fld
from sys.objects o
inner join sys.columns c on o.object_id =
c.object_id
inner join sys.index_columns i
on c.object_id = i.object_id
and c.column_id = i.column_id
inner join sys.key_constraints k
on i.object_id = k.parent_object_id
and i.index_id = k.unique_index_id
where k.type = 'PK') K
on M.table_name = K.table_name and M.column_name = K.column_name
/* Returns all of the indexed fields */
left outer join (select o.name as table_name, c.name as column_name,
min(index_column_id) as keyno
from sys.objects o
inner join sys.columns c on o.object_id =
c.object_id
inner join sys.index_columns i
on c.object_id = i.object_id
and c.column_id = i.column_id
group by o.name, c.name) I
on M.table_name = I.table_name and M.column_name = I.column_name
-----Original Message-----
From: dba-sqlserver-bounces at databaseadvisors.com
[mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of jwcolby
Sent: Thursday, December 06, 2007 2:54 PM
To: dba-sqlserver at databaseadvisors.com
Subject: [dba-SQLServer] Where do I find
What would the SQL look like to get a list of all the field names in a
table? I assume that there are system tables with this stuff but it is kind
of slow poking around trying to find that.
John W. Colby
Colby Consulting
www.ColbyConsulting.com
_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com