Using a Variable

Question

Using a Variable

dcoggin

SSC Veteran

Points: 278
More actions
October 3, 2002 at 2:32 am

#79324

Hi
I am trying to write a piece of SQL that will go through each database and return the tables within it. I am writing the results to a file.
I am using the table sysdatabases in master to run a cursor through the database names. I then want to use this as a prefix for the next cursor which runs through the sysobjects table in each database. However I dont know how to use the variable in the sql statement. As you can see in the SQL below I have got @dbases.sysobjects which does not work.
I just dont know what to do instead... I guess there are other ways of gettng this information but for my own education I wanted to write it myself.
truncate table dwtemp.dbo.tables
use master
declare @dbases varchar(50),
@tables varchar(50),
@sqlstring varchar(100)
declare itemcursors cursor fast_forward for select name from sysdatabases
open itemcursors
fetch next from itemcursors into @dbases
while @@fetch_status = 0
begin
print @dbases
declare itemcursor1 cursor fast_forward for select name from @dbases.sysobjects where xtype='U'
open itemcursor1
fetch next from itemcursor1 into @tables
while @@fetch_status = 0
begin
insert into dwtemp.dbo.tables
(DBname,TBname)
select @dbases, @tables
Fetch next from itemcursor1 into @tables
end
close itemcursor1
deallocate itemcursor1
fetch next from itemcursors into @dbases
end
close itemcursors
deallocate itemcursors

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Antares686 SSC Guru Points: 125444 More actions · Answer 1

To save yourself a lot of trouble there is a built in Procedure that can run a single piece of code against all databases.

sp_MSForEachDB SQLTASKHEREWITH?FORDBPOSITION

So something like this should work for you.

truncate table dwtemp.dbo.tables

--Note: dtproperties is technically system type table and exists in all databases.

EXEC sp_MSForEachDB 'INSERT dwtemp.dbo.tables (DBName, TBName) SELECT ''?'' DBName, [Name] TBName FROM sysobjects WHERE xtype=''U'' and [name] != ''dtproperties'''

SELECT * FROM dwtemp.dbo.tables

Edited by - antares686 on 10/03/2002 04:09:52 AM

skylon SSC Veteran Points: 243 More actions · Answer 2

I think you wil want to create a string and then execute it like so:

DECLARE @strsql nvarchar(1000) -- however long it needs to be

Then make all your sql statments strings that can be executed like:

@strsql = N'select name from ' [plus sign] @dbases [plus sign] N'.sysobjects where xtype='''U''''

[plus sign] - editor wont let me put the plus sign for some reason.

Then EXEC sp_executesql @strsql

You can do this for all your sql. DOnt know if this is what you are looking for. I see where you are passing variable names to the select, and you cant do this without creating a string and executing it. Hope I am not way off what you wanted,if so sorry.