SELECT TableName,
LTRIM(RTRIM(ColumnName)) AS ColumnName,
ColumnCNName,
CASE WHEN ColumnType = 'uniqueidentifier' THEN 'guid'
WHEN ColumnType IN('smallint', 'INT') THEN 'int'
WHEN ColumnType = 'BIGINT' THEN 'long'
WHEN ColumnType IN('CHAR', 'VARCHAR', 'NVARCHAR',
'text', 'xml', 'varbinary', 'image')
THEN 'string'
WHEN ColumnType IN('tinyint')
THEN 'byte'
WHEN ColumnType IN('bit') THEN 'bool'
WHEN ColumnType IN('time', 'date', 'DATETIME', 'smallDATETIME')
THEN 'DateTime'
WHEN ColumnType IN('smallmoney', 'DECIMAL', 'numeric',
'money') THEN 'decimal'
WHEN ColumnType = 'float' THEN 'float'
ELSE 'string '
END ColumnType,
CASE WHEN ColumnType IN ('NVARCHAR','NCHAR') THEN [Maxlength]/2 ELSE [Maxlength] END [Maxlength],
IsKey,
CASE WHEN ColumnName IN('CreateID', 'ModifyID', '')
OR IsKey = 1 THEN 0
ELSE 1
END AS IsDisplay ,
1 AS IsColumnData,
CASE WHEN ColumnType IN('time', 'date', 'DATETIME', 'smallDATETIME') THEN 150
WHEN ColumnName IN('Modifier', 'Creator') THEN 130
WHEN ColumnType IN('int', 'bigint') OR ColumnName IN('CreateID', 'ModifyID', '') THEN 80
WHEN[Maxlength] < 110 AND[Maxlength] > 60 THEN 120
WHEN[Maxlength] < 200 AND[Maxlength] >= 110 THEN 180
WHEN[Maxlength] > 200 THEN 220
ELSE 110
END AS ColumnWidth ,
0 AS OrderNo,
--CASE WHEN IsKey = 1 OR t.[IsNull]=0 THEN 0
-- ELSE 1 END
t.[IsNull] AS
[IsNull],
CASE WHEN IsKey = 1 THEN 1 ELSE 0 END IsReadDataset,
CASE WHEN IsKey!=1 AND t.[IsNull] = 0 THEN 0 ELSE NULL END AS EditColNo
FROM (SELECT obj.name AS TableName ,
col.name AS ColumnName ,
CONVERT(NVARCHAR(100),ISNULL(ep.[value], '')) AS ColumnCNName,
t.name AS ColumnType ,
CASE WHEN col.length<1 THEN 0 ELSE col.length END AS[Maxlength],
CASE WHEN EXISTS (SELECT 1
FROM dbo.sysindexes si
INNER JOIN dbo.sysindexkeys sik ON si.id = sik.id
AND si.indid = sik.indid
INNER JOIN dbo.syscolumns sc ON sc.id = sik.id
AND sc.colid = sik.colid
INNER JOIN dbo.sysobjects so ON so.name = si.name
AND so.xtype = 'PK'
WHERE sc.id = col.id
AND sc.colid = col.colid)
THEN 1
ELSE 0
END AS IsKey ,
CASE WHEN col.isnullable = 1 THEN 1
ELSE 0
END AS[IsNull],
col.colorder
FROM dbo.syscolumns col
LEFT JOIN dbo.systypes t ON col.xtype = t.xusertype
INNER JOIN dbo.sysobjects obj ON col.id = obj.id
AND obj.xtype IN ( 'U','V')
-- AND obj.status >= 01
LEFT JOIN dbo.syscomments comm ON col.cdefault = comm.id
LEFT JOIN sys.extended_properties ep ON col.id = ep.major_id
AND col.colid = ep.minor_id
AND ep.name = 'MS_Description'
LEFT JOIN sys.extended_properties epTwo ON obj.id = epTwo.major_id
AND epTwo.minor_id = 0
AND epTwo.name = 'MS_Description'
WHERE obj.name = '' --表名
) AS t
ORDER BY t.colorder
获取SQL Server表结构信息
最新推荐文章于 2023-09-08 11:45:19 发布