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

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和动态SQL
DECLARE @tb_schemas TABLE
　　(
　　db_name varchar(200),
    schemas_name varchar(200)
　　);
DECLARE @i INT;
DECLARE @sql VARCHAR(300);
SET @i = 1
  BEGIN
    WHILE @i <= (SELECT COUNT(*) FROM sys.databases)
    BEGIN
      SELECT @sql = ' use ' + A.name+';' + 'SELECT '+''''+A.name+''''+ ',name schemas_name FROM sys.schemas' FROM
      (
      SELECT A.name,ROW_NUMBER()OVER(order by database_id)  rn
      FROM 
      sys.databases A
      )A
      WHERE A.rn = @i
      INSERT INTO @tb_schemas EXEC(@sql);
      SET @i = @i + 1
    End
    SELECT 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

执行结果