Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
MSSQL - information_schema all dbs
drop table if exists #cols
select top 0 isc.*, cast(null as varchar(10)) as table_type, cast(null as bit) as is_primary
into #cols
from INFORMATION_SCHEMA.COLUMNS isc
declare @sql varchar(max)
set @sql = '
use ?
insert into #cols
select isc.*, ist.table_type, sys.fn_hadr_is_primary_replica(''?'')
from information_schema.columns isc
join information_schema.tables ist
on isc.table_name = ist.table_name
and isc.table_schema = ist.table_schema
where isc.table_catalog not in (''master'', ''model'', ''msdb'', ''tempdb'')
'
exec sp_MSforeachdb @sql
select count(*)
from #cols c
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.