Browse DevX
Sign up for e-mail newsletters from DevX

Tip of the Day
Language: SQL
Expertise: Intermediate
Mar 29, 2006



Building the Right Environment to Support AI, Machine Learning and Deep Learning

Retrieve All Columns from Each Table in Every Database

This stored procedure retrieves all the columns of each table in every database.

create procedure SP_GetAllColumnsFromAllDB
-- Procedure to retrieve columns of all the databases
-- Usage   : exec SP_GetAllColumnsFromAllDB
-- Logic is: First get all the DB into a master cursor 
    excluding system databases
-- (1 Master, 2 tempdb, 3 model, 4 msdb, 5 pubs, 6 northwind) 
-- if you want to include this then just remove the
   where condition from master cursor i.e.  where dbid&Gt6
-- loop through master cursor 
-- insert all table name into a temporary table #tmp_table
-- Create a inner cursor for tables
-- loop through this cursor
-- insert all the columns (into #final_table) of looping table(in Master looping database)
-- delete the table names from #tmp_table to be able to get the tables of next database 
-------------------------------------------------------------------- */ as begin declare @db_name varchar(1000) declare @table_name varchar(1000) declare @col_name varchar(1000) declare @id varchar(1000) declare @sql nvarchar(4000) create table #tmp_table (db varchar(100),name varchar(2000), id int) create table #final_table (db varchar(100),tname varchar(2000),cname varchar(2000)) -- Declare cursor for getting all the databases except -- system databases declare cur_db cursor for select name from master.dbo.sysdatabases where dbid>6 --excluding (1 Master,2 tempdb,3 model,4 msdb,5 pubs, --6 northwind) open cur_db fetch next from cur_db into @db_name while (@@fetch_status<>-1) begin -- Insert name of all the tables into #tmp_table for this -- database and use it set @sql='insert into #tmp_table (db,name,id) Select '''+@db_name+''', name,id from '+@db_name+'.dbo.'+'sysobjects where xtype in (''u'')' execute sp_executesql @sql --columns declare cur_column cursor for select * from #tmp_table open cur_column fetch next from cur_column into @db_name,@table_name,@id while (@@fetch_status<>-1) begin -- Insert column name into #final_table for this -- table (in which database this table belongs) set @sql='insert into #final_table (db,tname,cname) Select '''+@db_name+''''+','''+@table_name+''', name from '+@db_name+'.dbo.syscolumns where id =' +@id +' order by colorder' execute sp_executesql @sql fetch next from cur_column into @db_name,@table_name,@id end close cur_column deallocate cur_column set @sql='' -- Delete all rows from #tmp_table delete from #tmp_table fetch next from cur_db into @db_name end select db as 'database name',tname as 'table name',cname as 'Column' from #final_table order by db,tname close cur_db deallocate cur_db drop table #tmp_table drop table #final_table end
Mohammad Khan
Thanks for your registration, follow us on our social networks to keep up-to-date