List all Columns in Server Part 2

--create temp tables

create table #test(id int identity (1,1), DBName nvarchar(50))

create table #test1(id int identity (1,1), DBName nvarchar(50), TableName Nvarchar(200), columnName nvarchar(200), DataType nvarchar(100), Length int)

create table #test2(id int identity (1,1), DBName nvarchar(50), TableName Nvarchar(200), tbi int,columnName nvarchar(200))



--get a list of DBs

insert into #test(DBName)

select name from master.dbo.sysDatabases where sid !=0x01 



--declare variables

declare @i int

declare @mx int

declare @db nvarchar(50)

declare @tb nvarchar(100)

declare @sql nvarchar(4000)

declare @tbi int



declare @p int 

declare @max int

declare @s nvarchar(4000)

declare @dbname nvarchar(400)

declare @n nvarchar(10)

declare @tbid int

declare @tbl nvarchar(50)

declare @svr as nvarchar(50)









set @i=1

set @mx =(select max(id) from #test)+1

set @svr= @@servername

set @p=1







while @i <@mx

begin 

set @db = (select DBname from #test where @i=id)

set @sql ='select '''+@db+''' ,id,name from '+ @db+'.dbo.sysobjects

 where xtype =''u'''

set @i=@i+1

insert into #test2(DbName,tbi, TableName)

exec (@sql)





end

----------------

set @max=(select max(id) from #test2)+1



while @p < @max

begin 



set @dbname=(select dbname from #test2 where id=+ @p)

set @tbid=(select tbi from #test2 where id=+ @p)

set @tbl=(select tablename from #test2 where id=+ @p)





set @s='select '''+@dbname +''','''+@tbl+''' ,t.name,c.name, c.length from ['+@dbname +'].dbo.syscolumns c

    join dbo.systypes t 

 ON C.xusertype = T.xusertype

where id='+ cast(@tbid as nvarchar(20))



insert into #test1(dbname,Tablename,DataType,columnName, Length)

exec(@s)

--print(@s)

set @p=@p+1

end



--if exists(Select name from sysobjects where name ='MetaData')

--begin 

--drop table metadata

--end 



--Create Table MetaData(id int identity (1,1) not null,

--ServerName nvarchar(250),

--DatabaseName nvarchar(250),

--TableName nvarchar(250),

--ColumnName nvarchar(250),

--ColumnDataType nvarchar(250),

--ColumnDataLength nvarchar(20)

--)



--insert into MetaData

select @svr as Svr,

DBName DatabaseName,

TableName,

ColumnName,

DataType,

Length from #test1

order by tablename



--CleanUp

drop table #test

drop table #test1

drop table #test2





--Select * from MetaData