失眠网 > SQL Server查询所有数据库下模式schema

SQL Server查询所有数据库下模式schema

时间：2024-01-13 18:27:54

SQL Server查询所有数据库下模式名

需求描述

找到SQL Server里所有数据库下的schema.输出格式为数据库名、模式名。

背景补充

关于SQL Server需要简单介绍下，从数据库对象的粒度上可粗略的分为server、database、schema、table、colmun。这里server相当于instance（实例），一台机器可以装多个实例，一个实例有多个数据库，一个数据库有多个schema(模式），schema相当于把相似的表做个归类。比如零售系统里production模式归档是产品相关的表，sales模式则是销售相关的表。一个模式会有多个table（表），一个表会有多个字段(列)。

补充说明：一个schema可以赋予多个用户，一个用户也可以对应多个schema。

思路概述

#1 思路概述
1) 找到显示当前数据库下schema的系统视图。这里对应的是sys.schemas。
2) 找到可以查询所有数据库的系统视图，这里是sys.databases
3) 给所有数据库加个序号，以方便后续遍历。
4）通过WHILE循环匹配 3）里的序号。
5）拼接动态SQL生成每个数据库查询schema的SQL语句。
6）遍历执行并插入表变量里，最后展示查询结果。

TSQL代码

-- #方法1: 通过WHILE和动态SQLDECLARE @tb_schemas TABLE(db_name varchar(200),schemas_name varchar(200));DECLARE @i INT;DECLARE @sql VARCHAR(300);SET @i = 1BEGINWHILE @i <= (SELECT COUNT(*) FROM sys.databases)BEGINSELECT @sql = ' use ' + A.name+';' + 'SELECT '+''''+A.name+''''+ ',name schemas_name FROM sys.schemas' FROM(SELECT A.name,ROW_NUMBER()OVER(order by database_id) rnFROM sys.databases A)AWHERE A.rn = @iINSERT INTO @tb_schemas EXEC(@sql);SET @i = @i + 1EndSELECT DISTINCT db_name,schemas_name FROM @tb_schemas ORDER BY db_name;END-- 封装成存储过程仅需要在代码最前加创建存储语句即可(选做)。-- #方法2:用内置的系统存储过程sp_MSforeachdb结合临时表CREATE TABLE #schemaTable(dbname VARCHAR(100),schemaname VARCHAR(100))EXEC sp_MSforeachdb @command1="print '?'",@command2="INSERT INTO #schemaTable SELECT '?',name schemas_name FROM sys.schemas"SELECT * FROM #schemaTable