【推荐】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，如需转载请自行联系原作者
