sql server通过sql得到表结构 (sqlserver元数据查询)

1.表元数据获取

select
a.name AS tableName,/*表名*/
        isnull(g.[value],'') AS comment ,/*comment*/
a.create_date tableCreateTime/*表创建时间*/
from
information_schema.tables b
left join sys.tables a  on
b.TABLE_NAME = a.name
left join sys.extended_properties g on (a.object_id = g.major_id AND g.minor_id = 0)
where
b.TABLE_SCHEMA = '%s';/* %s schema 名*/

2.字段元数据获取

SELECT
CONVERT(nvarchar(50),ISNULL(S.[name], '')) as schemaname /*schemaname*/,
CONVERT(nvarchar(50),ISNULL(A.[name], '')) as tableName /*表名*/,
CONVERT(nvarchar(50),ISNULL(B.[name], '')) as columnName/*字段名*/,
CONVERT(nvarchar(50),ISNULL(C.[value], '')) as comment/*字段备注*/,
col.DATA_TYPE columnType/*数据类型*/,
col.NUMERIC_PRECISION  numericPrecision,
col.NUMERIC_SCALE  numericScale,
col.CHARACTER_MAXIMUM_LENGTH  columnLength,
col.IS_NULLABLE  isNullable   ,/*是否空*/
case when (B.name = (SELECT  COLUMN_NAME
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE OBJECTPROPERTY(OBJECT_ID(CONSTRAINT_SCHEMA+'.'+CONSTRAINT_NAME), 'IsPrimaryKey') = 1
AND TABLE_NAME = '%s')) then  1 ELSE   0 end as isPrimary /*是否主键 1 主键 0 不是 其中参数%s 是表名*/
FROM sys.tables A
INNER JOIN sys.schemas S
ON S.schema_id = A.schema_id
INNER JOIN sys.columns B
ON B.object_id = A.object_id
INNER JOIN information_schema.columns col
ON col.TABLE_SCHEMA  = S.name AND col.TABLE_NAME = A.name  AND col.COLUMN_NAME = B.name
LEFT JOIN sys.extended_properties C
ON C.major_id = B.object_id
AND C.minor_id = B.column_id
WHERE S.name = '%s' and A.name = '%s'    /*第一个%s参数是schemaName 第二个%s参数是表名 */

3.分页数据查询

SELECT * FROM (
        SELECT ROW_NUMBER() over(order by (
                select  top 1 c.name from sys.tables t ,sys.columns c  where t.object_id  = c.object_id and t.name ='%s.%s' ))  AS RowId,/*需要有一个排序字段 第一个%s 是schmaName 第二个%s 是表名 引号必须有*/
        *
        FROM %s.%s ado ) AS r/*第一个%s 是schmaName 第二个%s 是表名*/
WHERE RowId BETWEEN %s AND %s /*两个%s 是分页参数*/