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 = 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

執行結果

與50位技術專家面對面20年技術見證，附贈技術全景圖

總結

以上是生活随笔為你收集整理的SQL Server查询所有数据库下模式schema的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。