Copy Code code as follows:
--SQL Server 2000
SELECT a.name As field name, case when EXISTS
(SELECT 1
From sysobjects
WHERE xtype = ' PK ' and parent_obj = a.id and name in
(SELECT name
From sysindexes
WHERE Indid in
(SELECT indid
From Sysindexkeys
WHERE id = a.id and colid = A.colid))
THEN ' 1 ' ELSE ' 0 ' end as primary key, case when ColumnProperty (a.ID, A.name,
' isidentity ') = 1 THEN ' 1 ' ELSE ' 0 ' End as identity, b.name as type,
A.length as occupies the number of bytes, ColumnProperty (a.id, A.name, ' PRECISION ') as length,
A.xscale as decimal, a.isnullable as Nullable, ISNULL (E.text, ') as default value, ISNULL (G.[value),
") as Field description
From syscolumns a left OUTER JOIN
Systypes b on a.xusertype = B.xusertype INNER JOIN
sysobjects d on a.id = d.id and D.xtype = ' U ' and
D.name <> ' dtproperties ' left OUTER JOIN
syscomments e on a.cdefault = e.id left OUTER JOIN
Sysproperties g on a.id = g.id and A.colid = G.smallid left OUTER JOIN
Sysproperties F on d.id = f.id and f.smallid = 0
WHERE (d.name = ' table name ')
--2. SQL SERVER 2005
SELECT Case When EXISTS
(SELECT 1
From sysobjects
WHERE xtype = ' PK ' and parent_obj = a.id and name in
(SELECT name
From sysindexes
WHERE Indid in
(SELECT indid
From Sysindexkeys
WHERE id = a.id and colid = a.colid)) THEN ' 1 ' ELSE ' 0 ' end as ' key ', case when ColumnProperty (a.ID, A.name,
' isidentity ') = 1 THEN ' 1 ' ELSE ' 0 ' end as ' identity ', a.name as ColName, c.name as TypeName, a.length as ' byte ', COLUMNPR Operty (a.ID, A.name,
' PRECISION ') as ' length ', A.xscale, A.isnullable, ISNULL (E.text, ') as ' default ', ISNULL (P.value, ') as ' comment '
From Sys.syscolumns as a INNER JOIN
Sys.sysobjects as B on a.id = b.ID INNER JOIN
Sys.systypes as C on a.xtype = C.xtype left OUTER JOIN
Sys.syscomments as E on a.cdefault = e.id left OUTER JOIN
Sys.extended_properties as P on a.id = p.major_id and a.colid = p.minor_id
WHERE (b.name = ' keyfactory ') and (C.status <> ' 1 ')
--b.name = ' keyfactory ', ' keyfactory ' for the data table you want to find.
--2, SQL SERVER 2005
SELECT Case When EXISTS
(SELECT 1
From sysobjects
WHERE xtype = ' PK ' and parent_obj = a.id and name in
(SELECT name
From sysindexes
WHERE Indid in
(SELECT indid
From Sysindexkeys
WHERE id = a.id and colid = a.colid)) THEN ' 1 ' ELSE ' 0 ' end as ' key ', case when ColumnProperty (a.ID, A.name,
' isidentity ') = 1 THEN ' 1 ' ELSE ' 0 ' end as ' identity ', a.name as ColName, c.name as TypeName, a.length as ' byte ', COLUMNPR Operty (a.ID, A.name,
' PRECISION ') as ' length ', A.xscale, A.isnullable, ISNULL (E.text, ') as ' default ', ISNULL (P.value, ') as ' comment '
From Sys.syscolumns as a INNER JOIN
Sys.sysobjects as B on a.id = b.ID INNER JOIN
Sys.systypes as C on a.xtype = C.xtype left OUTER JOIN
Sys.syscomments as E on a.cdefault = e.id left OUTER JOIN
Sys.extended_properties as P on a.id = p.major_id and a.colid = p.minor_id
WHERE (b.name = ' keyfactory ') and (C.status <> ' 1 ')
--b.name = ' keyfactory ', ' keyfactory ' for the data table you want to find.