Sometimes it’s necessary or nice to export all the databases and tables on a server. This simple script will give you information about every table you have access. As a DBA, you can uncomment the “execute as…” and “revert” lines to determine what tables another user can see.

-- execute as login = 'newUser'
set nocount on

Declare @Output as table (DBName varchar(200), TableName varchar(200), TAB_VIEW varchar(10), ColName varchar(200), ColType varchar(200), PK bit)
declare @DBName as VARCHAR(100) = 'master'
declare @SQL as NVARCHAR(max)
declare @CurDB as int = 5

 

while @CurDB <= (select MAX(database_id) from master.sys.databases)
begin
SELECT @DBName = name from master.sys.databases where database_id = @CurDB

begin try
print 'Checking ' + @DBName + '…'
SET @SQL = 'select "' + @DBName + "' as DBName, tables.name as TableName, tables.TAB_VIEW, columns.name as ColName,
case types.name
when "varchar" then "varchar(" + ISNULL(NULLIF(NULLIF(convert(varchar(4),columns.max_length) ,-1),0),"MAX") + ")"
when "char" then "char(" + ISNULL(NULLIF(NULLIF(convert(varchar(4),columns.max_length) ,-1),0),"MAX") + ")"
when "nvarchar" then "nvarchar(" + ISNULL(NULLIF(NULLIF(convert(varchar(4),columns.max_length/2),-1),0),"MAX") + ")"
when "nchar" then "nchar(" + ISNULL(NULLIF(NULLIF(convert(varchar(4),columns.max_length/2),-1),0),"MAX") + ")"
when "binary" then "binary(" + ISNULL(NULLIF(NULLIF(convert(varchar(4),columns.max_length) ,-1),0),"MAX") + ")"
when "varbinary" then "varbinary(" + ISNULL(NULLIF(NULLIF(convert(varchar(4),columns.max_length) ,-1),0),"MAX") + ")"

when "numeric" then "numeric(" + convert(varchar(4),columns.precision) + "," + convert(varchar(4),columns.scale) + ")"
when "decimal" then "decimal(" + convert(varchar(4),columns.precision) + "," + convert(varchar(4),columns.scale) + ")"

when "float" then "float" + ISNULL(NULLIF("("+convert(varchar(4),columns.precision)+")","(53)"),"")
–when "varbinary" then "varbinary(" + ISNULL(NULLIF(NULLIF(convert(varchar(4),columns.max_length) ,-1),0),"MAX") + ")"
else types.name end as ColType–, columns.*
,isnull(PK.[PrimaryKey],0) as [PrimaryKey]

from [' + @DBName + '].sys.columns
inner join (SELECT NAME, object_id, is_ms_shipped, "TABLE" AS TAB_VIEW FROM [' + @DBName + '].sys.tables
union SELECT NAME, object_id, is_ms_shipped, "VIEW" AS TAB_VIEW FROM [' + @DBName + '].sys.views ) as tables
on tables.object_id = columns.object_id
left join [' + @DBName + '].sys.types
on types.user_type_id = columns.user_type_id
outer apply (
select case when si.object_id is not null then 1 else 0 end as [PrimaryKey]
from [' + @DBName + '].sys.indexes si
inner join [' + @DBName + '].sys.index_columns ic
ON si.OBJECT_ID = ic.OBJECT_ID
AND si.index_id = ic.index_id
and si.is_primary_key = 1
where si.object_id = tables.object_id
and ic.column_id = columns.column_id
) as PK

where tables.is_ms_shipped = 0'

insert @Output
exec sp_sqlexec @SQL
print '... Success'
end try
begin catch
print '... Error: ' + error_message()
end catch

set @CurDB += 1
end

select * from @Output

print 'COMPLETE!'
-- revert