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)
 Iterate through all schemas & run t-sql

Author  Topic 

mattt
Posting Yak Master

194 Posts
Posted - 2011-03-10 : 07:11:32
Hi,

I've got a short t-sql script that creates a cursor on a table and runs through all the rows, performing some basic maintenance.

The database always runs against the database "dbname" which can have multiple schemas, but each schema will always have a table of name "tablename". It's this table I want to run my script against. The database name and the tablename are fixed remember, but "schemaname" is variable: 


Declare c1 cursor read_only
For
Select intRowId from [dbname].[schemaname].[tablename]

Declare @trackingID int

Open c1

Fetch next from c1 into @trackingID

While @@fetch_status = 0
Begin

-- do stuff
	
Fetch next from c1 into @trackingID
End

Close c1
Deallocate c1


Is there a way of iterating through all the schemas, making sure that the select statement to create the cursor uses the right schema name for the current schema and running the cursor contents against "tablename" in that schema?

Cheers,
Matt

Lumbago
Norsk Yak Master

3271 Posts
Posted - 2011-03-10 : 07:58:57
Ok, I need to say first that this sounds like a really horrible idea. It would be really interesting to see the "-- do stuff" part...

But you can get the schema name by doing a
SELECT TABLE_SCHEMA FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'tablename'

- Lumbago
My blog-> http://thefirstsql.com/2011/02/07/regular-expressions-advanced-string-matching-and-new-split-function-sql-server-2008-r2/
Go to Top of Page

mattt
Posting Yak Master

194 Posts
Posted - 2011-03-10 : 08:06:17
quote:
Originally posted by Lumbago

Ok, I need to say first that this sounds like a really horrible idea. It would be really interesting to see the "-- do stuff" part...

But you can get the schema name by doing a
SELECT TABLE_SCHEMA FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'tablename'


Thanks, will check it out. Not immediately obvious to me how I can use that data in my query without restoring to dynamic SQL?

In the meantime the "to do" is quite simple. It references an xml field for the row and looks for a certain key. If that key has a value, it inserts it into a different field in the same row. It's for speed of processing later down the line - better to do it upfront when the data is loaded instead of trying to pull out that value each and every time we need to use it.
Go to Top of Page

Lumbago
Norsk Yak Master

3271 Posts
Posted - 2011-03-10 : 08:24:55
It's not possible to query a dynamic range of tables without using dynamic sql (there is the sp_msforeachtable-procedure but that would not be suitable I think).

But why are you doing this "do stuff" in a cursor loop? Set based is always a lot better and a lot more efficient...

- Lumbago
My blog-> http://thefirstsql.com/2011/02/07/regular-expressions-advanced-string-matching-and-new-split-function-sql-server-2008-r2/
Go to Top of Page

mattt
Posting Yak Master

194 Posts
Posted - 2011-03-10 : 08:35:01
quote:
Originally posted by Lumbago



But why are you doing this "do stuff" in a cursor loop? Set based is always a lot better and a lot more efficient...


The answer is probably "ignorance". Care to point me at some suitable information?
Go to Top of Page

Lumbago
Norsk Yak Master

3271 Posts
Posted - 2011-03-10 : 08:47:12
Well, you can always google "set based vs cursor". But if you want specific advice you'll need to show us what you're doing. If the "do stuff" is business sensitive, try to illustrate the problem using different table/column names etc.

- Lumbago
My blog-> http://thefirstsql.com/2011/02/07/regular-expressions-advanced-string-matching-and-new-split-function-sql-server-2008-r2/
Go to Top of Page

mattt
Posting Yak Master

194 Posts
Posted - 2011-03-10 : 08:58:27
quote:
Originally posted by Lumbago

Well, you can always google "set based vs cursor". But if you want specific advice you'll need to show us what you're doing. If the "do stuff" is business sensitive, try to illustrate the problem using different table/column names etc.


Your persistent assistance is much appreciated. Here's the full whack - I only left it out originally because I thought it was irrelevant:


use [dbname]

Declare c1 cursor read_only
For
Select intRowId from [dbname].[schemaname].[tablename]

Declare @trackingID int

Open c1

Fetch next from c1 into @trackingID

While @@fetch_status = 0
Begin

	if (select strGroupId from [dbname].[schemaname].[tablename] where intRowId = @trackingID) is null
	begin
		declare @groupId nvarchar(255)
		
		select @groupId = xmlSource.value('
		declare default element namespace "xmlSchemaname";
		(/RootNode/ChildNode[Key="soughtKeyValue"]/Value)[1]', 'nvarchar(255)')
		from [dbname].[schemaname].[tablename]
		where intRowId = @trackingID
		
		update [dbname].[schemaname].[tablename] set strGroupId = @groupId where intRowId = @trackingID
	end
	
Fetch next from c1 into @trackingID
End

Close c1
Deallocate c1
Go to Top of Page

Lumbago
Norsk Yak Master

3271 Posts
Posted - 2011-03-10 : 09:21:36
Well...I have a feeling that you have renamed at least two different tables to [dbname].[schemaname].[tablename] so it's a little hard to make something that's "plug'n'play" but I think this should be pretty close to what you need to do. I'm also not very good with this xml-stuff so you might have to do some tweaking there as well:

update a
	set a.strGroupId = b.xmlSource.value('declare default element namespace "xmlSchemaname";
		(/RootNode/ChildNode[Key="soughtKeyValue"]/Value)[1]', 'nvarchar(255)')
from [dbname].[schemaname].[tablename] a
	inner join [dbname].[schemaname].[tablename] b
		on a.intRowId = b.intRowId
where a.strGroupId is null


- Lumbago
My blog-> http://thefirstsql.com/2011/02/07/regular-expressions-advanced-string-matching-and-new-split-function-sql-server-2008-r2/
Go to Top of Page

Lumbago
Norsk Yak Master

3271 Posts
Posted - 2011-03-10 : 09:27:09
And if you make it work for you it will do everything in one go...no need for he cursor.

- Lumbago
My blog-> http://thefirstsql.com/2011/02/07/regular-expressions-advanced-string-matching-and-new-split-function-sql-server-2008-r2/
Go to Top of Page

mattt
Posting Yak Master

194 Posts
Posted - 2011-03-10 : 09:28:48
Ah but that's just it - I haven't changed two table names. This is operating on the same table. Each row has a frequently used value (groupId) that's hidden in an xml field. Accessing that value is costly because the xml is often quite large and we're looking to match a single value. So the purpose of this excercise is to do a one-time process that grabs the value and puts it in a field on its own in the same row, so that it's more readily available for use in other transactions. Hence the cursor.
Go to Top of Page

Lumbago
Norsk Yak Master

3271 Posts
Posted - 2011-03-10 : 09:32:26
I'm sorry for the spamming of this topic, I'm just bored basically. But if this is in fact all the same table then it could be even simpler:
update [dbname].[schemaname].[tablename]
	set strGroupId = xmlSource.value('declare default element namespace "xmlSchemaname";
		(/RootNode/ChildNode[Key="soughtKeyValue"]/Value)[1]', 'nvarchar(255)')
where strGroupId is null


- Lumbago
My blog-> http://thefirstsql.com/2011/02/07/regular-expressions-advanced-string-matching-and-new-split-function-sql-server-2008-r2/
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site