dynamic query construction - SQL Server Forums

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 2005 Forums

Transact-SQL (2005)

dynamic query construction

Author

Topic

mobius
Starting Member

13 Posts

Posted - 2007-06-28 : 17:36:52

This is my first stored procedure ever and I'm trying to use cursors to create a table with a list of all of the tables and their database names in our server. If there's some system table or something that I could just query, I would love life again :-P, but for now, this is what I've got.


ALTER PROCEDURE sag_tst
AS

DECLARE @DB nvarchar(30)
DECLARE @TBL nvarchar(30)
DECLARE @dbcurse CURSOR
DECLARE @tabcurse CURSOR


DELETE FROM master.dbo.tbl_db_tab

SET @dbcurse = CURSOR FAST_FORWARD
FOR
SELECT name FROM master.sys.databases

OPEN @dbcurse
FETCH NEXT FROM @dbcurse 
INTO @DB

WHILE @@FETCH_STATUS=0
BEGIN
	
	SET @tabcurse = CURSOR FAST_FORWARD
	FOR
	SELECT name FROM @DB.sys.database
	OPEN @tabcurse
	FETCH NEXT FROM @tabcurse
	
	WHILE @@FETCH_STATUS=0
	BEGIN
		INSERT INTO master.dbo.tbl_db_tab (DBNAM,TBLNAM) values(@DB,@TBL)
		FETCH NEXT FROM @tabcurse
		INTO @TBL
	END
	
	CLOSE @tabcurse
	FETCH NEXT FROM @dbcurse
	INTO @DB
END
CLOSE @tabcurse
DEALLOCATE @dbcurse
DEALLOCATE @tabcurse
GO

The problem is that @DB doesn't work the way I want it to in this phrase:


SET @tabcurse = CURSOR FAST_FORWARD
FOR
SELECT name FROM @DB.sys.database

Is there a way for me to cycle through databases like that?

karuna
Aged Yak Warrior

582 Posts

Posted - 2007-06-28 : 17:59:24

How about sys.objects?

Thanks
Karunakaran

mobius
Starting Member

13 Posts

Posted - 2007-06-28 : 18:10:05

Umm, I'm not sure how that would work given that each database has it's own sys.objects view. Is there something similar that spans the server?

mobius
Starting Member

13 Posts

Posted - 2007-06-29 : 10:56:12

/bump

mobius
Starting Member

13 Posts

Posted - 2007-06-29 : 16:04:27

Upon further review, it looks like some form of dynamic SQL might be the answer. So I've gotten this far:


ALTER PROCEDURE sag_tst
AS

DECLARE @DB sysname
DECLARE @TBL nvarchar(30)
DECLARE @dbcurse CURSOR
DECLARE @tabcurse CURSOR

DECLARE @sql nvarchar(100)


DELETE FROM master.dbo.tbl_db_tab

SET @dbcurse = CURSOR FAST_FORWARD
FOR
SELECT name FROM master.sys.databases

OPEN @dbcurse
FETCH NEXT FROM @dbcurse 
INTO @DB

WHILE @@FETCH_STATUS=0
BEGIN
	
	SELECT @sql = 'select name from ' + quotename(@DB) + '.sys.database'
	
	SET @tabcurse = CURSOR FAST_FORWARD
	FOR
	select name from EXEC sp_executesql @sql, N'@DB nvarchar(30)', @DB --SELECT name FROM @DB.sys.database

	OPEN @tabcurse
	FETCH NEXT FROM @tabcurse
	
	WHILE @@FETCH_STATUS=0
	BEGIN
		INSERT INTO master.dbo.tbl_db_tab (DBNAM,TBLNAM) values(@DB,@TBL)
		FETCH NEXT FROM @tabcurse
		INTO @TBL
	END
	
	CLOSE @tabcurse
	FETCH NEXT FROM @dbcurse
	INTO @DB
END
CLOSE @tabcurse
DEALLOCATE @dbcurse
DEALLOCATE @tabcurse
GO

But I'm now getting an error at the exec statement.


select name from EXEC sp_executesql @sql, N'@DB nvarchar(30)', @DB --SELECT name FROM @DB.sys.database

I used the 'select name' part because I figured executing that sql would return a table type, though I'm not sure.

I've seen a lot of anti-cursor sentiment on the forums but right now I don't see any other way. You can't do unions between databases very well because of differing collations.

Haywood
Posting Yak Master

221 Posts

Posted - 2007-06-29 : 17:47:32

Much easier than you thought...


SET NOCOUNT ON
GO

DECLARE @DBName sysname
		, @DynSQL varchar(max)


CREATE TABLE ##_DB_TableList
	( DBName sysname
		, TableName sysname )


DECLARE curList CURSOR 
FOR SELECT [name] 
		FROM master.sys.databases 
		WHERE [name] NOT IN ('model', 'tempdb')
		ORDER BY [name]

OPEN curList

FETCH NEXT FROM curList INTO @DBName
WHILE (@@fetch_status <> -1)
BEGIN
	IF (@@fetch_status <> -2)
	BEGIN

		SET @DynSQL = '

		USE [' + @DBName + ']

		INSERT INTO ##_DB_TableList
			SELECT ''' + @DBName + '''
				, [name]

			FROM sys.objects
			WHERE Type = ''U''
			AND is_ms_shipped != 1
		'

		EXEC (@DynSQL);

END
	FETCH NEXT FROM curList INTO @DBName
END

CLOSE curList
DEALLOCATE curList
GO

SELECT *
	FROM ##_DB_TableList
GO

Edit: Forgot that you only wanted tables...

mobius
Starting Member

13 Posts

Posted - 2007-07-02 : 11:07:33

O.o I wanna have your kids. Karuna, I get what you mean now, and thanks for beating it through my head haywood.

Haywood
Posting Yak Master

221 Posts

Posted - 2007-07-02 : 12:09:16

Iterating through databases via dynamic sql is a bit on the tricky side, but once you see it....

You're quite welcome, too. :)

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2007-07-02 : 12:20:03

Is this what you are looking for? I realize its not as fance and cursory, but it gets the job done.

EDIT: sigh. should try it before i post it. Here is a non cursor, but its just as bad






declare @foo table (i int identity(1,1) , dbname varchar(150))
declare @bar int
declare @sql varchar(500)
declare @a varchar(500)
declare @b int

insert into @foo select catalog_Name from information_schema.SCHEMATA
select @b =  max(i) from @foo
set @bar = 1

while @bar <= @b
begin

select @a = dbname from @foo where i = @bar
select @sql = 'USE ' + @a 
+ '  
select s.catalog_Name, t.table_name
from information_schema.SCHEMATA s
inner join information_schema.tables t
on s.catalog_name = t.table_catalog'

exec (@sql)
 set @bar = @bar + 1
end

[Signature]For fast help, follow this link:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
Learn SQL
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

mobius
Starting Member

13 Posts

Posted - 2007-07-02 : 14:22:27

Yeah, hehe, you basically used a cursor without using a cursor :-P. Though I suppose you may have saved a bit of memory allocating an int instead of a cursor. I'm honored to have 2 yaks posting on my first thread. Hopefully I'll be able to make some contributions of my own eventually.

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2007-07-03 : 07:34:00

If you stick around long enough, you will contribute.

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2011-02-10 : 09:04:57

OK -- no loops here (not visible ones anyway!)


DECLARE @sql NVARCHAR(MAX) SET @sql = N''

SELECT @sql = @sql + N'
UNION SELECT
	' + QUOTENAME(d.[name], '''') + ' AS [DatabaseName]
	, o.[name] AS [TableName]
FROM
	' + QUOTENAME(d.[name]) + '.sys.objects AS o
WHERE
	o.[Type] = ''U'' AND o.[is_ms_shipped] = 0'
FROM
	master.sys.databases AS d
WHERE
	d.[name] NOT IN ('model', 'tempdb')

-- Strip the first UNION FROM the dynamic sql
SET @sql = RIGHT(@sql, LEN(@sql) - 8)

-- PRINT @sql
EXEC sp_executesql @sql

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2011-02-10 : 09:07:50

Oops -- didn't realise this thread was so old!

It was on Kristen's Interesting Threads list that I hadn't seen before.

BUMP!

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources