五餅二魚工作室

臨時朋友傳來一個訊息，要整理一下所有資料庫內有那些的資料表，就隨手寫了一下，有需要的朋友可以參考看看

DROP TABLE IF EXISTS #AllTables;

CREATE TABLE #AllTables (
		DatabaseName sysname,
		SchemaName sysname,
		TableName sysname,
)
EXEC [sys].[sp_MSforeachdb] 'use [?];insert into #AllTables select "?",schema_name(schema_id),name from ?.sys.tables where type = "U" and "?" not in ("master","tempdb","msdb") ' ;

SELECT * FROM #AllTables

既然完成所有資料表 , 那也就順手把所有欄位也一併完成好了

DROP TABLE IF EXISTS #AllColumns;

CREATE TABLE #AllColumns (
		DatabaseName sysname,
		SchemaName sysname,
		TableName  sysname,
		ColumnName sysname,
		ColumnIndex int,
		ColumnType sysname
)
EXEC [sys].[sp_MSforeachdb] 
	'use [?];insert into #AllColumns select "?",schema_name(t.schema_id),t.name, c.name, column_id, p.name 
		from sys.columns c
		JOIN sys.tables t ON c.object_id = t.object_id
		JOIN sys.types p ON c.user_type_id = p.user_type_id
		where t.type = "U" and "?" not in ("master","tempdb","msdb") ORDER BY t.name,c.column_id  ' ;

SELECT * FROM #AllColumns ORDER BY DatabaseName,SchemaName,TableName, ColumnIndex;