Get the Tables information of each databse

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Get the Tables information of each databse

Author

Topic

vijays3
Constraint Violating Yak Guru

354 Posts

Posted - 2011-03-28 : 23:39:55

Hi all,

I want to get the each database's tables information into a single table ..I Tried to implement it by using cursor and dynamic query ..But not successed ..Below is my script which i have created to accomplished my requirement ..Can any one suggest me is their any better way to get the tables information ...

declare @sql varchar(max)
declare @db_name varchar(200)
select * into #temp1 from sys.databases where name not in ('master','tempdb','model','msdb')
create table #temp2 ( dbname varchar(200),schemaName varchar(200),tableName varchar(200))
DECLARE cur CURSOR LOCAL
FOR select name from #temp1
open cur

while (@@FETCH_STATUS = 0)
begin

set @sql = 'use '+ @db_name +';
drop table #temp2
create table #temp2 ( dbname varchar(200),schemaName varchar(200),tableName varchar(200))
insert into #temp2 select DB_NAME()as DatabasName, SCHEMA_NAME(schema_id) schema_name, name as table_name --, *
FROM sys.tables
WHERE OBJECTPROPERTY(object_id,''TableHasClustIndex'') = 0
and type_desc like ''%user%''
Order by 1,2'
print(@sql)

FETCH NEXT FROM cur INTO @db_name
end
close cur
deallocate cur
select * from #temp2

drop table #temp1

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-03-29 : 01:20:00

firstly i assumed you will have a exec (@sql) after the print @sql statement

the problem with your query is you are creating the temporary table #temp2 in the exec(). The Temp table will be out of scope when exec() exit and it will not be accessible after exec().

you can remove the create #temp2 from the @sql, and insert the result of exec(@sql) into the #temp2


insert into #temp2 ( <column list> )
exec (@sql)

KH
[spoiler]Time is always against us[/spoiler]

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2011-03-29 : 19:22:15

[CODE]declare @sql nvarchar(4000) =
'use

; -- Square brackets around an asterisk

insert into #temp2
select DB_NAME()as DatabasName, SCHEMA_NAME(schema_id) schema_name, name as table_name --, *
FROM sys.tables
WHERE OBJECTPROPERTY(object_id,''TableHasClustIndex'') = 0
and type_desc like ''%user%''
Order by 1,2'[/CODE]
-----------------------------------------------------------------------------
--- Populate table
-----------------------------------------------------------------------------

exec sp_msforeachdb @sql

=======================================
Elitism is the slur directed at merit by mediocrity. -Sydney J. Harris, journalist (1917-1986)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources