CREATE PROCEDURE [Common]. [Procloadcolumninfo]
@inObjectName sysname
As
BEGIN
SELECT Tablecatalog = db_name (),
Tableschema = Schema_name (c.schema_id),
TableName = D.name,
ColumnName = A.name,
ColumnPosition = A.colorder,
DataType = B.name,
MaximumLength = A.length,
MaxLength = ColumnProperty (a.id, A.name, ' PRECISION '),
NumericScale = ISNULL (ColumnProperty (a.id, a.name, ' scale '), 0),
isidentity = case when ColumnProperty (a.ID, A.name,
' isidentity ') = 1 then 1
ELSE 0
END,
Isprimary = case When EXISTS (SELECT 1
From Sys.indexes idx,
Sys.index_columns Idxcol
WHERE idx.object_id = a.id
and Idx.is_primary_key = 1
and idxcol.column_id = A.colid
and idx.index_id = idxcol.index_id
and idx.object_id = idxcol.object_id)
Then 1
ELSE 0
END,
IsNullable = a.isnullable,
Columndefault = ISNULL (E.text, "),
Columndescription = ISNULL (G.[value], ")
From Syscolumns A
Left JOIN systypes B on a.xtype = B.xusertype
INNER JOIN sys.objects C on a.id = c.object_id
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 sys.extended_properties G on a.id = g.major_id
and a.colid = g.minor_id
and g.name = ' ms_description '
WHERE a.id = object_id (@inObjectName)
ORDER by a.ID,
A.colorder;
END;
GO
SQL SERVER gets table field information