SELECT T.name AS TableName
, C.colorder AS ColOrder
, C.name AS ColName
, CASE WHEN C.xtype = 34 THEN 'IMAGE'
WHEN C.xtype = 35 THEN 'TEXT'
WHEN C.xtype = 36 THEN 'UNIQUEIDENTIFIER'
WHEN C.xtype = 40 THEN 'DATE'
WHEN C.xtype = 41 THEN 'TIME('
+ CONVERT(VARCHAR,C.xscale) + ')'
WHEN C.xtype = 42 THEN 'DATETIME2('
+ CONVERT(VARCHAR,C.xscale) + ')'
WHEN C.xtype = 48 THEN 'TINYINT'
WHEN C.xtype = 52 THEN 'SMALLINT'
WHEN C.xtype = 56 THEN 'INT'
WHEN C.xtype = 58 THEN 'SMALLDATETIME'
WHEN C.xtype = 59 THEN 'REAL'
WHEN C.xtype = 60 THEN 'MONEY'
WHEN C.xtype = 61 THEN 'DATETIME'
WHEN C.xtype = 62 THEN 'FLOAT'
WHEN C.xtype = 98 THEN 'SQL_VARIANT'
WHEN C.xtype = 99 THEN 'NTEXT'
WHEN C.xtype = 104 THEN 'BIT'
WHEN C.xtype = 106 THEN 'DECIMAL('
+ CONVERT(VARCHAR,C.xprec) + ','
+ CONVERT(VARCHAR,C.xscale)+ ')'
WHEN C.xtype = 108 THEN 'NUMERIC('
+ CONVERT(VARCHAR,C.xprec) + ','
+ CONVERT(VARCHAR,C.xscale)+ ')'
WHEN C.xtype = 122 THEN 'SMALLMONEY'
WHEN C.xtype = 127 THEN 'BIGINT'
WHEN C.xtype = 165 THEN 'VARBINARY'
WHEN C.xtype = 167 THEN
CASE WHEN C.prec>0
THEN 'VARCHAR('
+ CONVERT(VARCHAR,C.prec) + ')'
ELSE 'VARCHAR(MAX)' END
WHEN C.xtype = 173 THEN 'BINARY('
+ CONVERT(VARCHAR,C.prec) + ')'
WHEN C.xtype = 175 THEN 'CHAR('
+ CONVERT(VARCHAR,C.prec) + ')'
WHEN C.xtype = 189 THEN 'TIMESTAMP'
WHEN C.xtype = 231 THEN
CASE WHEN C.prec>0
THEN 'NVARCHAR('
+ CONVERT(VARCHAR,C.prec) + ')'
ELSE 'NVARCHAR(MAX)' END
WHEN C.xtype = 239 THEN 'NCHAR('
+ CONVERT(VARCHAR,C.prec) + ')'
ELSE '??? ' + CONVERT(VARCHAR,C.xtype) + ' ???' END
AS ColType
, C.isnullable AS ColNullable
FROM sysobjects T
INNER JOIN syscolumns C On C.ID = T.ID
WHERE T.xtype = 'U'
ORDER BY T.Name, C.ColOrder
No comments:
Post a Comment