Login | Register   
RSS Feed
Download our iPhone app
Browse DevX
Sign up for e-mail newsletters from DevX

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

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
Comment and Contribute






(Maximum characters: 1200). You have 1200 characters left.



Thanks for your registration, follow us on our social networks to keep up-to-date