Dynamic SQL - 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

General SQL Server Forums

New to SQL Server Programming

Dynamic SQL

Author

Topic

tclose
Starting Member

24 Posts

Posted - 2010-04-24 : 17:12:37

I am trying to iterate through all of the tables in a database. I was told I could do this with dynamic SQL, but I am unable to find information on how I could extract the name of each table to do SELECTS on and such. Any ideas on this or where to look for this information.

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-04-24 : 17:45:17

What version of SQL Server?

------------------------------------------------------------------------------------
Any and all code contained within this post comes with a 100% money back guarantee.

tclose
Starting Member

24 Posts

Posted - 2010-04-24 : 17:46:26

quote:
Originally posted by DBA in the making

What version of SQL Server?

------------------------------------------------------------------------------------
Any and all code contained within this post comes with a 100% money back guarantee.

SQL SERVER 2008

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-04-24 : 18:00:43

Try this. It'll work on 2005, and probably 2008 as well. I can't test it on 2008 ATM.

DECLARE @SchemaName sysname 
DECLARE @TableName sysname 
DECLARE @SQL VARCHAR(8000) 

DECLARE cr CURSOR FOR
SELECT s.name AS SchemaName, t.name as TableName
FROM sys.tables t
INNER JOIN sys.schemas s
	ON t.schema_id = s.schema_id
ORDER BY s.name, t.name

OPEN cr

FETCH FROM cr into @SchemaName, @TableName

WHILE @@FETCH_STATUS = 0
BEGIN
	SET @SQL = 'PRINT ''' + @SchemaName + '.' + @TableName + ''''
	EXEC(@SQL)

	SET @SQL = 'SELECT COUNT(*) FROM [' + @SchemaName + '].[' + @TableName + ']'
	EXEC(@SQL)
	FETCH FROM cr into @SchemaName, @TableName
END

CLOSE cr
DEALLOCATE cr

------------------------------------------------------------------------------------
Any and all code contained within this post comes with a 100% money back guarantee.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-04-24 : 18:18:43

Without cursor:
http://www.sqlservercentral.com/scripts/Miscellaneous/30900/

No, you're never too old to Yak'n'Roll if you're too young to die.

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-04-24 : 18:51:20

quote:
Originally posted by webfred

Without cursor:
http://www.sqlservercentral.com/scripts/Miscellaneous/30900/

That has a limit of 8000 total characters. Large table lists combined with large commands will break this, worst case, without causing an error. Also, it doesn't consider schema.

------------------------------------------------------------------------------------
Any and all code contained within this post comes with a 100% money back guarantee.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-25 : 02:07:17

the limit of 8000 can be dispensed with by making variable varchar(max) type

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

tclose
Starting Member

24 Posts

Posted - 2010-04-25 : 03:52:58

quote:
Originally posted by DBA in the making

Try this. It'll work on 2005, and probably 2008 as well.

Run as is your script does what it is suppose to do. When I tried to change it to an INSERT statement - it doesn't. I will have to learn more about this subject in order to get it to do what I want. Thanks for the starter.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources