【推荐】Sql Server 不常见应用之一
获取表的基本信息、字段列表、存储过程参数列表
——通过知识共享树立个人品牌。
一、获取表的基本信息
SELECT [TableName] = [Tables].name ,
[TableOwner] = [Schemas].name ,
[TableCreateDate] = [Tables].create_date ,
[TableModifyDate] = [Tables].modify_date
FROM sys.tables AS [Tables]
INNER JOIN sys.schemas AS [Schemas] ON [Tables].schema_id = [Schemas].schema_id
WHERE [Tables].name = '案卷目录'
[TableOwner] = [Schemas].name ,
[TableCreateDate] = [Tables].create_date ,
[TableModifyDate] = [Tables].modify_date
FROM sys.tables AS [Tables]
INNER JOIN sys.schemas AS [Schemas] ON [Tables].schema_id = [Schemas].schema_id
WHERE [Tables].name = '案卷目录'
效果如图一所示：
图一 指定表的基本信息
二、根据表名获取字段列表
SELECT [ColumnName] = [Columns].name ,
[SystemTypeName] = [Types].name ,
[Precision] = [Columns].precision ,
[Scale] = [Columns].scale ,
[MaxLength] = [Columns].max_length ,
[IsNullable] = [Columns].is_nullable ,
[IsRowGUIDCol] = [Columns].is_rowguidcol ,
[IsIdentity] = [Columns].is_identity ,
[IsComputed] = [Columns].is_computed ,
[IsXmlDocument] = [Columns].is_xml_document ,
[Description] = [Properties].value
FROM sys.tables AS [Tables]
INNER JOIN sys.columns AS [Columns] ON [Tables].object_id = [Columns].object_id
INNER JOIN sys.types AS [Types] ON [Columns].system_type_id = [Types].system_type_id
AND is_user_defined = 0
AND [Types].name <> 'sysname'
LEFT OUTER JOIN sys.extended_properties AS [Properties] ON [Properties].major_id = [Tables].object_id
AND [Properties].minor_id = [Columns].column_id
AND [Properties].name = 'MS_Description'
WHERE [Tables].name =案卷目录
ORDER BY [Columns].column_id
[SystemTypeName] = [Types].name ,
[Precision] = [Columns].precision ,
[Scale] = [Columns].scale ,
[MaxLength] = [Columns].max_length ,
[IsNullable] = [Columns].is_nullable ,
[IsRowGUIDCol] = [Columns].is_rowguidcol ,
[IsIdentity] = [Columns].is_identity ,
[IsComputed] = [Columns].is_computed ,
[IsXmlDocument] = [Columns].is_xml_document ,
[Description] = [Properties].value
FROM sys.tables AS [Tables]
INNER JOIN sys.columns AS [Columns] ON [Tables].object_id = [Columns].object_id
INNER JOIN sys.types AS [Types] ON [Columns].system_type_id = [Types].system_type_id
AND is_user_defined = 0
AND [Types].name <> 'sysname'
LEFT OUTER JOIN sys.extended_properties AS [Properties] ON [Properties].major_id = [Tables].object_id
AND [Properties].minor_id = [Columns].column_id
AND [Properties].name = 'MS_Description'
WHERE [Tables].name =案卷目录
ORDER BY [Columns].column_id
效果如图二所示：
图二 指定表的字段列表信息
三、获取指定存储过程参数列表
SELECT sc.name AS 参数名 ,
st.name AS 类型 ,
sc.length AS 长度
FROM syscolumns sc
INNER JOIN sysobjects so ON so.id = sc.id
INNER JOIN systypes st ON sc.xtype = st.xtype
WHERE so.name = 'SP_Pagination'
st.name AS 类型 ,
sc.length AS 长度
FROM syscolumns sc
INNER JOIN sysobjects so ON so.id = sc.id
INNER JOIN systypes st ON sc.xtype = st.xtype
WHERE so.name = 'SP_Pagination'
效果如图三所示：
图三 指定存储过程的参数列表
本文转自yonghu86 51CTO博客，原文链接：http://blog.51cto.com/yonghu/1321322，如需转载请自行联系原作者