ALTER procedure GetTableColumns
@ TableName varchar (100)
As
Begin
----------------- Start
SELECT
D. name n' table name ',
A. colorder n' Field Sequence Number ',
A. name n' field name ',
(Case when COLUMNPROPERTY (a. id, a. name, 'isidentity ') = 1 then' √ 'else' 'end) n' ',
(Case when (SELECT count (*)
FROM sysobjects
WHERE (name in
(SELECT name
FROM sysindexes
WHERE (id = a. id) AND (indid in
(SELECT indid
FROM sysindexkeys
WHERE (id = a. id) AND (colid in
(SELECT colid
FROM syscolumns
WHERE (id = a. id) AND (name = a. name) AND
(Xtype = 'pk')> 0 then' √ 'else' end) n'primary key ',
B. name n' type ',
A. length N 'number of bytes occupied ',
COLUMNPROPERTY (a. id, a. name, 'precision ') as N 'length ',
Isnull (COLUMNPROPERTY (a. id, a. name, 'Scale'), 0) as N 'decimal ',
(Case when a. isnullable = 1 then '√ 'else' 'end) n' allow null ',
Isnull (e. text, '') n' default value ',
Isnull (g. [value], '') AS n' field description'
-- Into # tx
FROM syscolumns a left join policypes B
On a. xtype = B. xusertype
Inner join sysobjects d
On a. id = d. id and d. xtype = 'U' and d. name <> 'dtproperties'
Left join syscomments e
On a. cdefault = e. id
Left join sysproperties g
On a. id = g. id AND a. colid = g. smallid where d. name = @ TableName
Order by object_name (a. id), a. colorder
---------------------------------------------- End
End