获取数据库、表、列和列数据类型的列表

27 浏览
0 Comments

获取数据库、表、列和列数据类型的列表

我想获取所有用户数据库的集合，每个数据库中的所有表，以及每个表的列和它们的数据类型。我有一个SQL脚本可以获取我要查找的数据库名称（并排除我不需要的数据库，如系统数据库）...

DECLARE @DatabaseIgnoreList AS VARCHAR(500);

SET @DatabaseIgnoreList = 'DatabaseNotToInclude';

SELECT name, database_id

FROM sys.databases

WHERE HAS_DBACCESS(name) = 1

AND name NOT IN ('msdb')

AND database_id > 4

AND name NOT LIKE '%$%'

AND name NOT IN (@DatabaseIgnoreList)

ORDER BY name ASC

我还有一个单独的SQL脚本，可以从特定的数据库中获取所有表和列。我从这个SO问题/回答中找到了这个脚本。

USE DatabaseName;

SELECT TABLE_SCHEMA ,

TABLE_NAME ,

COLUMN_NAME ,

ORDINAL_POSITION ,

COLUMN_DEFAULT ,

DATA_TYPE ,

CHARACTER_MAXIMUM_LENGTH ,

NUMERIC_PRECISION ,

NUMERIC_PRECISION_RADIX ,

NUMERIC_SCALE ,

DATETIME_PRECISION

FROM INFORMATION_SCHEMA.COLUMNS;

我不确定如何将这两个脚本连接起来。对于每个数据库，我想运行表/列发现脚本，并最终得到一个包含数据库名称、表名称、列名称和列数据类型的单个表。

这是否需要使用游标？我使用的是SQL Server 2008+。

0

2 答案

0 Comments

这不是最好的方法，但它可以工作。声明一个变量为VARCHAR(500)，将其设为'DatabaseNotToInclude'。从sys.databases中选择name和database_id，将结果存入#temporaltable中，条件为HAS_DBACCESS(name) = 1、name不包括'msdb'、database_id大于4、name不包含'%$%'、name不在空集合中，并按name升序排序。声明两个变量为varchar(250)和varchar(max)。声明一个游标，将#temporaltable中的name作为选择集。打开游标，并将结果存入变量中。当游标的状态为0时，执行一段动态SQL，查询INFORMATION_SCHEMA.COLUMNS中的相关列信息。获取下一个结果，直至游标结束。关闭游标，释放游标，删除#temporaltable表。

谢谢你提供的例子。你提到这不是最好的方法。你能描述一下你的意思，或者提供一个更好的技术吗？

嗯，此刻我想不出其他获得所需信息的方法，但是运行一个游标，假设数据库中有许多表和字段，SQL Server的性能将受到极大的影响。

0
0 Comments

问题的出现原因：需要获取数据库、表、列和列数据类型的列表。

解决方法：使用一个查询语句来获取所有的数据。代码中有详细的注释。通过查询sys.databases和INFORMATION_SCHEMA.COLUMNS来获取数据库、表、列和列数据类型的信息。使用UNION ALL将查询结果合并，并按照指定的顺序进行排序。最后，执行动态SQL语句来获取最终的结果。

以下是解决方法的代码：

DECLARE @DatabaseNotToInclude AS VARCHAR(500);
SET @DatabaseNotToInclude = 'DatabaseNotToInclude';
DECLARE @sql nvarchar(max) = '';
SELECT @sql = @sql + 
    'SELECT ''' + quotename(name) + ''' as DatabaseName, 
       TABLE_SCHEMA = TABLE_SCHEMA collate database_default,
       TABLE_NAME = TABLE_NAME COLLATE database_default ,
       COLUMN_NAME = COLUMN_NAME COLLATE database_default ,
       ORDINAL_POSITION ,
       COLUMN_DEFAULT = COLUMN_DEFAULT COLLATE database_default ,
       DATA_TYPE = DATA_TYPE COLLATE database_default ,
       CHARACTER_MAXIMUM_LENGTH ,
       NUMERIC_PRECISION ,
       NUMERIC_PRECISION_RADIX ,
       NUMERIC_SCALE ,
       DATETIME_PRECISION
    FROM ' + quotename(name) + '.INFORMATION_SCHEMA.COLUMNS union all '
FROM sys.databases 
WHERE HAS_DBACCESS(name) = 1 
    AND database_id > 4 
    AND name NOT LIKE '%$%' 
    AND name <> @DatabaseNotToInclude 
    AND state_desc = 'ONLINE' 
ORDER BY name ASC;
SET @sql = left(@sql, LEN(@sql) - 10) + ' order by DatabaseName, TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME';
-- Uncomment the line below when you are satisfied the dynamic sql is correct
-- EXEC sp_executesql @sql;

非常感谢提供帮助的评论。

0