CURSOR problem or USE problem? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

General SQL Server Forums

New to SQL Server Programming

CURSOR problem or USE problem?

Author

Topic

Bill_C
Constraint Violating Yak Guru

299 Posts

Posted - 2009-02-18 : 10:17:02

[code]
DECLARE @tablename1 nvarchar(100)
DECLARE tables_cursor CURSOR FOR

SELECT name FROM sys.Databases WHERE owner_sid <> 0x01
OPEN tables_cursor
FETCH NEXT FROM tables_cursor INTO @tablename1

USE @tablename1;

[/code]

I'm trying to use the above to scroll through every user database on the server but it fails at the line 'USE @tablename1'

Can I not pass a variable to the 'USE' statement?

Any way around this so I can scroll through all the databases one by one using the 'USE' statement within a Cursos (as i want to create a new table inside the cursor inside every database found)?

Thanks

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-02-18 : 10:22:30

quote:
Originally posted by Bill_C

DECLARE @tablename1 nvarchar(100)
DECLARE tables_cursor CURSOR FOR

SELECT name FROM sys.Databases WHERE owner_sid <> 0x01
OPEN tables_cursor
FETCH NEXT FROM tables_cursor INTO @tablename1

USE @tablename1;

I'm trying to use the above to scroll through every user database on the server but it fails at the line 'USE @tablename1'

Can I not pass a variable to the 'USE' statement?

Any way around this so I can scroll through all the databases one by one using the 'USE' statement within a Cursos (as i want to create a new table inside the cursor inside every database found)?

Thanks

You can create a table in another db while you are sitting in another db as long as you have permissions.

Something like,

create table anotherDB..testing (a int)

You don't need to use 'USE' there.

Bill_C
Constraint Violating Yak Guru

299 Posts

Posted - 2009-02-18 : 10:22:37

I want to create a table in every database found, this table will contain the names of every table & view found within each database,
I need to do it this way so I can read the table created above from Visual Basic and then use the names of each table found within every database on the server.

Bill_C
Constraint Violating Yak Guru

299 Posts

Posted - 2009-02-18 : 10:26:49

I also need to check if the table exists first, here is the full code, it works OK for one database if i explicitly name it, but as said above, I want to go through all the db's found.



DECLARE @tablename1 nvarchar(100)
DECLARE tables_cursor CURSOR FOR 


SELECT name FROM sys.Databases WHERE owner_sid <> 0x01
	OPEN tables_cursor
		FETCH NEXT FROM tables_cursor INTO @tablename1
SET @tablename1 = rtrim(@tablename1)
USE @tablename1;

IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'DB_TableStructure')
BEGIN
	DROP TABLE DB_TableStructure
END
--GO
--
--create the table to receive the data
CREATE TABLE	dbo.[DB_TableStructure]
		(
			table_cat VARCHAR(50),
			table_schema VARCHAR(20),
			table_name VARCHAR(50),
			table_type VARCHAR(50),
			create_date DATETIME,
			modify_date DATETIME
		)

INSERT DB_TableStructure
select TABLE_CATALOG,TABLE_SCHEMA,TABLE_NAME,TABLE_TYPE,'','' 
from INFORMATION_SCHEMA.TABLES ORDER BY TABLE_NAME
--GO

UPDATE DB_TableStructure
SET DB_TableStructure.create_date = sys.tables.create_date, 
DB_TableStructure.modify_date = sys.tables.modify_date
FROM sys.tables
WHERE DB_TableStructure.table_name = sys.tables.name
--GO

select * from DB_TableStructure order by table_type


		IF @@FETCH_STATUS <> 0 
			PRINT '         <<None>>'     

	WHILE @@FETCH_STATUS = 0
BEGIN

FETCH NEXT FROM tables_cursor INTO @tablename1
-----------------------------
USE @tablename1

IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'DB_TableStructure')
BEGIN
	DROP TABLE DB_TableStructure
END
--GO
--
--create the table to receive the data
CREATE TABLE	dbo.[DB_TableStructure]
		(
			table_cat VARCHAR(50),
			table_schema VARCHAR(20),
			table_name VARCHAR(50),
			table_type VARCHAR(50),
			create_date DATETIME,
			modify_date DATETIME
		)

INSERT DB_TableStructure
select TABLE_CATALOG,TABLE_SCHEMA,TABLE_NAME,TABLE_TYPE,'','' 
from INFORMATION_SCHEMA.TABLES ORDER BY TABLE_NAME
--GO

UPDATE DB_TableStructure
SET DB_TableStructure.create_date = sys.tables.create_date, 
DB_TableStructure.modify_date = sys.tables.modify_date
FROM sys.tables
WHERE DB_TableStructure.table_name = sys.tables.name
--GO

select * from DB_TableStructure order by table_type

END

	CLOSE tables_cursor
	DEALLOCATE tables_cursor

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-02-18 : 10:38:21

One way is,
Replace the created,checks and the updates to something like this

exec 
	('CREATE TABLE '+tablename1+'.dbo.[DB_TableStructure]
		(
			table_cat VARCHAR(50),
			table_schema VARCHAR(20),
			table_name VARCHAR(50),
			table_type VARCHAR(50),
			create_date DATETIME,
			modify_date DATETIME
		)
	')

You don't need to then use "USE" in your code. You can fire it from any db and achieve the same thing.

Alternatively, You can compile a view in all dbs, which would give you the same information. This ways, If incase there are any changes to table structure etc, you wouldn't have to rerun your code.

You can also do the same using just one view.

Bill_C
Constraint Violating Yak Guru

299 Posts

Posted - 2009-02-18 : 10:42:50

Thanks, I thought I'd use the script to delete & re-create the table every time the script was run so then I would have an up to date table in each db.
So this would not be possible then?

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2009-02-18 : 10:44:54

Try this, I haven't tested it mind, but been working on something similar, except that I have to dynamically create tables from many different datasources and the selects from them.


DECLARE @tablename1 nvarchar(100), @SqlCmd nvarchar(max)
DECLARE tables_cursor CURSOR FOR 


SELECT name FROM sys.Databases WHERE owner_sid <> 0x01
	OPEN tables_cursor
		FETCH NEXT FROM tables_cursor INTO @tablename1
SET @tablename1 = rtrim(@tablename1)
USE @tablename1;

IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'DB_TableStructure')
BEGIN
	DROP TABLE DB_TableStructure
END
--GO
--
--create the table to receive the data
CREATE TABLE	dbo.[DB_TableStructure]
		(
			table_cat VARCHAR(50),
			table_schema VARCHAR(20),
			table_name VARCHAR(50),
			table_type VARCHAR(50),
			create_date DATETIME,
			modify_date DATETIME
		)

INSERT DB_TableStructure
select TABLE_CATALOG,TABLE_SCHEMA,TABLE_NAME,TABLE_TYPE,'','' 
from INFORMATION_SCHEMA.TABLES ORDER BY TABLE_NAME
--GO

UPDATE DB_TableStructure
SET DB_TableStructure.create_date = sys.tables.create_date, 
DB_TableStructure.modify_date = sys.tables.modify_date
FROM sys.tables
WHERE DB_TableStructure.table_name = sys.tables.name
--GO

select * from DB_TableStructure order by table_type


		IF @@FETCH_STATUS <> 0 
			PRINT '         <<None>>'     

WHILE @@FETCH_STATUS = 0
BEGIN

FETCH NEXT FROM tables_cursor INTO @tablename1
-----------------------------
	--USE @tablename1

	set @SqlCmd = 'IF EXISTS (SELECT * FROM ' + @Databasename + '.INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = ''DB_TableStructure'')
	BEGIN
		DROP TABLE ' + @Databasename + '..DB_TableStructure
	END
	--GO
	--
	--create the table to receive the data
	CREATE TABLE	' + @Databasename + '.dbo.[DB_TableStructure]
			(
				table_cat VARCHAR(50),
				table_schema VARCHAR(20),
				table_name VARCHAR(50),
				table_type VARCHAR(50),
				create_date DATETIME,
				modify_date DATETIME
			)

	INSERT ' + @Databasename + '..DB_TableStructure
	select TABLE_CATALOG,TABLE_SCHEMA,TABLE_NAME,TABLE_TYPE,'','' 
	from ' + @Databasename + '.INFORMATION_SCHEMA.TABLES ORDER BY TABLE_NAME
	--GO

	UPDATE ' + @Databasename + '..DB_TableStructure
	SET create_date = sys.tables.create_date, 
		modify_date = sys.tables.modify_date
	FROM ' + @Databasename + '.sys.tables t
	WHERE table_name = t.name
	--GO

	select * from ' + @Databasename + '..DB_TableStructure order by table_type')
	exec sp_executesql @SqlCmd

END

CLOSE tables_cursor
DEALLOCATE tables_cursor

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-02-18 : 10:45:12

quote:
Originally posted by Bill_C

Thanks, I thought I'd use the script to delete & re-create the table every time the script was run so then I would have an up to date table in each db.
So this would not be possible then?

It is very much possible. Your way is just not the best way to do this if you ask me. Why don't you considering using a view to arrive at the same.

Bill_C
Constraint Violating Yak Guru

299 Posts

Posted - 2009-02-18 : 10:49:57

True,
I didn't think of that.
Thanks

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-02-18 : 10:56:42

So, You could compile a view like this in all schemas where you need this,


Create view DB_TableStructure as
select top 100 percent a.TABLE_CATALOG,a.TABLE_SCHEMA,a.TABLE_NAME,a.TABLE_TYPE,  b.CREATE_DATE, b.MODIFY_DATE
from INFORMATION_SCHEMA.TABLES a,sys.tables b
where a.table_name = b.name
ORDER BY TABLE_NAME

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-18 : 12:07:06

also you dont need cusrosr to loop through all the dbs. you can use the system stored procedure sp_msforeachdb for creating this in each db of your server. just use


EXEC sp_msforeachdb 'USE ?
Create view DB_TableStructure as
select top 100 percent a.TABLE_CATALOG,a.TABLE_SCHEMA,a.TABLE_NAME,a.TABLE_TYPE,  b.CREATE_DATE, b.MODIFY_DATE
from INFORMATION_SCHEMA.TABLES a,sys.tables b
where a.table_name = b.name
ORDER BY TABLE_NAME'

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-18 : 12:09:12

also see

http://www.databasejournal.com/features/mssql/article.php/3441031/SQL-Server-Undocumented-Stored-Procedures-spMSforeachtable-and-spMSforeachdb.htm

Bill_C
Constraint Violating Yak Guru

299 Posts

Posted - 2009-02-19 : 02:56:36

Thanks both.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-19 : 03:01:38

welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources