List all DBs, Tables, and Columns

    No Comments

    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

     

    Categories: Scripts, SQL Server

    Leave a Reply

    Your email address will not be published. Required fields are marked *