CodeCREATE TABLE TableTmp(ID int NOT NULL IDENTITY,
COL1 sysname,COL3 sysname,COL4 sysname,
COL5 sysname,COL6 sysname,COL7 sysname,COL8 sysname,
COL9 sysname)
DECLARE c CURSOR FAST_FORWARD FOR SELECT name FROM sys.tables ORDER BY name ASC
DECLARE @TbName sysname
OPEN c
FETCH NEXT FROM c INTO @TbName
WHILE @@FETCH_STATUS=0
BEGIN
INSERT INTO TableTmp(COL1,COL3,COL4,COL5,COL6,COL7,COL8,COL9)
SELECT '表名','','','','','','',''
UNION ALL
SELECT @TbName AS 表名,'','','','','','',''
INSERT INTO TableTmp(COL1,COL3,COL4,COL5,COL6,COL7,COL8,COL9)
SELECT '列名' AS 列名,'类型定义' AS 类型定义,'是否为空' AS 是否为空,
'是否标识' AS 是否标识,'标识定义' AS 标识定义,'默认定义' AS 默认定义,'是否主键' AS 是否主键,
'备注' AS 备注
INSERT INTO TableTmp(COL1,COL3,COL4,COL5,COL6,COL7,COL8,COL9)
SELECT c.name AS 列名,
CASE WHEN p.name='numeric' THEN p.name+'('+CAST(c.precision AS VARCHAR)+','+CAST(c.scale AS VARCHAR)+')'
WHEN p.name='varchar' THEN p.name+'('+CAST(c.max_length AS VARCHAR)+')'
ELSE p.name
END AS 类型定义,
CASE WHEN c.is_nullable=1 THEN 'NOT NULL'
ELSE 'NULL'
END AS 是否为空,
CASE WHEN c.is_identity=1 THEN 'Yes'
ELSE 'No'
END AS 是否标识,
CASE WHEN c.is_identity=1 THEN '('+CAST(i.seed_value AS VARCHAR)+','+CAST(i.increment_value AS VARCHAR)+')'
ELSE ''
END AS 标识定义,
CASE WHEN d.definition IS NOT NULL THEN SubString(d.definition,2,Len(d.definition)-2)
ELSE ''
END AS 默认定义,
CASE WHEN k.COLUMN_NAME IS NOT NULL THEN 'Yes'
ELSE 'No'
END AS 是否主键,
'' AS 备注
FROM sys.columns AS c
INNER JOIN sys.tables AS t ON c.object_id=t.object_id
INNER JOIN sys.types AS p ON c.user_type_id=p.user_type_id
LEFT JOIN sys.default_cONstraints AS d ON c.default_object_id=d.object_id
LEFT JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS k ON t.name=k.TABLE_NAME AND c.column_id=k.ORDINAL_POSITION AND k.CONSTRAINT_NAME LIKE 'PK%'
LEFT JOIN sys.identity_columns AS i ON c.object_id=i.object_id AND c.column_id=i.column_id
WHERE t.name=@TbName
ORDER BY c.column_id ASC
INSERT INTO TableTmp(COL1,COL3,COL4,COL5,COL6,COL7,COL8,COL9)
SELECT '','','','','','','',''
FETCH NEXT FROM c INTO @TbName
END
CLOSE c
DEALLOCATE c
SELECT COL1,COL3,COL4,COL5,COL6,COL7,COL8,COL9 FROM TableTmp ORDER BY ID ASC
TRUNCATE TABLE TableTmp
DROP TABLE TableTmp
浙公网安备 33010602011771号