[sql] 撈取所有DB 的 所有TABLE

最近有個需求是直接給一張table, 但卻不知道在眾多DB的哪一個DB裡面

因此股哥到列出所有DB 及其 Table 並整理如下:

	--讀取所有db
	select type, name,quotename(name,''''),state from master.sys.databases
	--query db table
	select * FROM [DB].sys.tables
	select * from [DB].sys.schemas
	--query dbo table name
	SELECT 'master' as database_name,
	s.name COLLATE DATABASE_DEFAULT as schema_name,
	t.name COLLATE DATABASE_DEFAULT as table_name
	FROM [master].sys.tables t
	JOIN [master].sys.schemas s on s.schema_id = t.schema_id
	--query all db and tables
	declare @sql nvarchar(max);
	select @sql =
	stuff((select ' UNION ALL
	SELECT ' + + quotename(name,'''') + ' as database_name,
	s.name COLLATE DATABASE_DEFAULT as schema_name,
	t.name COLLATE DATABASE_DEFAULT as table_name
	FROM '+ quotename(name) + '.sys.tables t
	JOIN '+ quotename(name) + '.sys.schemas s on s.schema_id = t.schema_id'
	from sys.databases
	where state=0
	order by [name] for xml path(''), type).value('.', 'nvarchar(max)'), 1,12,'');
	select @sql;
	set @sql = 'select * from (' + @sql + ') d where table_name = ''queryTableName'' order by database_name, schema_name,table_name';
	execute (@sql);

view raw sql list all db and table.sql hosted with ❤ by GitHub