Code
CREATE 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
CREATE 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
· ASP.NET Core - 日志记录系统(二)
· .NET 依赖注入中的 Captive Dependency
· .NET Core 对象分配(Alloc)底层原理浅谈
· 聊一聊 C#异步 任务延续的三种底层玩法
· 敏捷开发:如何高效开每日站会
· 终于决定:把自己家的能源管理系统开源了!
· 互联网不景气了那就玩玩嵌入式吧,用纯.NET开发并制作一个智能桌面机器人(一):从.NET IoT入
· C#实现 Winform 程序在系统托盘显示图标 & 开机自启动
· ASP.NET Core - 日志记录系统(二)
· 实现windows下简单的自动化窗口管理