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

admin管理员组

文章数量:1794759

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 执行结果

本文标签： 模式数据库SQLSERVERschema