【推荐】Sql Server 不常见应用之一：获取表的基本信息、字段列表、存储过程参数列表...-优快云博客

【推荐】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 = ' 案卷目录 '

效果如图一所示：

图一指定表的基本信息

二、根据表名获取字段列表

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

效果如图二所示：

图二指定表的字段列表信息

三、获取指定存储过程参数列表

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 '

效果如图三所示：