Retrieve All Columns from Each Table in Every Database

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≫6-- 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 
-------------------------------------------------------------------- */asbegin 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 '''[email protected]_name+''', name,id from '[email protected]_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 '''[email protected]_name+''''+','''[email protected]_name+''', name from '[email protected]_name+'.dbo.syscolumns where id =' [email protected] +' 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_tableend
Share the Post:
Heading photo, Metadata.

What is Metadata?

What is metadata? Well, It’s an odd concept to wrap your head around. Metadata is essentially the secondary layer of data that tracks details about the “regular” data. The regular

XDR solutions

The Benefits of Using XDR Solutions

Cybercriminals constantly adapt their strategies, developing newer, more powerful, and intelligent ways to attack your network. Since security professionals must innovate as well, more conventional endpoint detection solutions have evolved

AI is revolutionizing fraud detection

How AI is Revolutionizing Fraud Detection

Artificial intelligence – commonly known as AI – means a form of technology with multiple uses. As a result, it has become extremely valuable to a number of businesses across

AI innovation

Companies Leading AI Innovation in 2023

Artificial intelligence (AI) has been transforming industries and revolutionizing business operations. AI’s potential to enhance efficiency and productivity has become crucial to many businesses. As we move into 2023, several