none
SQL SERVER – 2008 R2 – List All The Column With Specific Data Types from all databases

    Question

  • Hi All,

    I have multiple databases like Adventure works and northwind.

    I want a single query that will get list all the column with datatypes from all databases along database base name.

    Thursday, July 18, 2013 4:13 AM

Answers

  • Hi Na'Win' kumar,

    I'm slightly modifying Satheesh's query...try this:

    create table #temp 
    (
    	databasename sysname,
    	tablename sysname, 
    	columnname sysname, 
    	typename sysname
    )
    GO
    
    sp_msforeachdb 'insert into #temp 
    				select ''?'' databasename,t.name tablename,c.name columnname,ty.name typename 
    				from [?].sys.tables t 
    				join [?].sys.columns c 
    					on t.object_id=c.object_id 
    				join [?].sys.types ty
    					on c.user_type_id=ty.user_type_id and c.system_type_id=ty.system_type_id
    				WHERE ''?'' NOT IN (''master'',''msdb'',''tempdb'',''model'')';
    GO
    
    select * from #temp;
    GO
    
    DROP TABLE #temp;
    GO


    Krishnakumar S

    Thursday, July 18, 2013 6:23 AM
  • For to add USE statement
    create table #temp (databasename sysname,
    tablename sysname,
    columnname sysname,
    typename sysname) exec sp_msforeachdb ' USE [?] insert into #temp select ''[?]'' databasename,
    t.name tablename,c.name columnname,
    t.name typename from sys.tables t
    join sys.columns c on t.object_id=c.object_id
    join sys.types ty on c.user_type_id=ty.user_type_id and c.system_type_id=ty.system_type_id' select * from #temp


    Satheesh

    Thursday, July 18, 2013 6:26 AM

All replies

  • Try this
    create table #temp (databasename sysname,tablename sysname, columnname sysname, typename sysname)
    sp_msforeachdb 'insert into #temp select [?] databasename,t.name tablename,c.name columnname,t.name typename from sys.tables t join sys.columns c on t.object_id=c.object_id join sys.types ty
    on c.user_type_id=ty.user_type_id and c.system_type_id=ty.system_type_id'
    
    select * from #temp


    Satheesh


    Thursday, July 18, 2013 4:35 AM
  • Hi Sateesh,

    Thanks for your reply.

    I tried your query with minor change as i wrote "exec" before sp_msforeachdb then it giving error as invalid column name..

    Thursday, July 18, 2013 4:43 AM
  • Can you post the query you changed?

    Satheesh

    Thursday, July 18, 2013 5:18 AM
  • create table #temp (databasename sysname,tablename sysname, columnname sysname, typename sysname)
    exec sp_msforeachdb 'insert into #temp select [?] databasename,t.name tablename,c.name columnname,t.name typename from sys.tables t join sys.columns c on t.object_id=c.object_id join sys.types ty
    on c.user_type_id=ty.user_type_id and c.system_type_id=ty.system_type_id'

    select * from #temp
    Thursday, July 18, 2013 5:21 AM
  • Okay. I fixed it

    create table #temp (databasename sysname,tablename sysname, columnname sysname, typename sysname)
    exec sp_msforeachdb 'insert into #temp select ''[?]'' databasename,t.name tablename,c.name columnname,t.name typename from sys.tables t join sys.columns c on t.object_id=c.object_id join sys.types ty
    on c.user_type_id=ty.user_type_id and c.system_type_id=ty.system_type_id'
    
    select * from #temp
    


    Satheesh

    Thursday, July 18, 2013 5:36 AM
  • Hi Sateesh,

    The query giving list of tables from where it is triggring and cross join with all databases which are existed. but not giving tables list relates to all databases.

    For example if we are triggering this query from adventureworks DB it giving list of tables from adventureworks and doing cross join with all the DBs like adventureworks, master, northwind. but it is not giving actual tables existed in northwind DB

    Thursday, July 18, 2013 5:50 AM
  • In your initial post you said you need all database. If you need for a specific database run this query 
    select t.name tablename,c.name columnname,t.name typename from sys.tables t join sys.columns c on t.object_id=c.object_id join sys.types ty
    on c.user_type_id=ty.user_type_id and c.system_type_id=ty.system_type_id


    Satheesh

    Thursday, July 18, 2013 6:01 AM
  • Hi Na'Win' kumar,

    I'm slightly modifying Satheesh's query...try this:

    create table #temp 
    (
    	databasename sysname,
    	tablename sysname, 
    	columnname sysname, 
    	typename sysname
    )
    GO
    
    sp_msforeachdb 'insert into #temp 
    				select ''?'' databasename,t.name tablename,c.name columnname,ty.name typename 
    				from [?].sys.tables t 
    				join [?].sys.columns c 
    					on t.object_id=c.object_id 
    				join [?].sys.types ty
    					on c.user_type_id=ty.user_type_id and c.system_type_id=ty.system_type_id
    				WHERE ''?'' NOT IN (''master'',''msdb'',''tempdb'',''model'')';
    GO
    
    select * from #temp;
    GO
    
    DROP TABLE #temp;
    GO


    Krishnakumar S

    Thursday, July 18, 2013 6:23 AM
  • For to add USE statement
    create table #temp (databasename sysname,
    tablename sysname,
    columnname sysname,
    typename sysname) exec sp_msforeachdb ' USE [?] insert into #temp select ''[?]'' databasename,
    t.name tablename,c.name columnname,
    t.name typename from sys.tables t
    join sys.columns c on t.object_id=c.object_id
    join sys.types ty on c.user_type_id=ty.user_type_id and c.system_type_id=ty.system_type_id' select * from #temp


    Satheesh

    Thursday, July 18, 2013 6:26 AM
  • Thanks to Sateesh and krishna for your quires.

    Quires areworking fine

    Thursday, July 18, 2013 6:35 AM