Looping code through tables in a SQl Server database

Lenny Gray
Lenny Gray used Ask the Experts™
on
I developed code that lists the columns, types and attributes (below) and it lists what I need.
Capture3.PNGIn SysObjects, I can see xtype = 'u' as the selection criteria but how can I iterate through all tables in the database so that I will get a continuous listing of the columns, types and attributes?

select
    t.TABLE_NAME as 'Table', c.COLUMN_NAME as 'Column Name' ,c.DATA_TYPE as 'Type' ,c.CHARACTER_MAXIMUM_LENGTH as 'Length',c.NUMERIC_PRECISION as 'Numeric length',c.NUMERIC_SCALE as 'Decimals',c.DATETIME_PRECISION as 'Date/Time'
    from INFORMATION_SCHEMA.Tables t
        INNER JOIN INFORMATION_SCHEMA.Columns c ON t.TABLE_CATALOG=c.TABLE_CATALOG AND t.TABLE_SCHEMA=c.TABLE_SCHEMA AND t.TABLE_NAME=c.TABLE_NAME
    WHERE t.TABLE_NAME='assetprice'
    ORDER BY t.TABLE_NAME, c.COLUMN_NAME
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Senior .Net Developer
Commented:
You can use a cursor to loop over the set:
http://www.mssqltips.com/sqlservertip/1599/sql-server-cursor-example/

You would need to remove the table name in the where clause to get all of the tables.

Author

Commented:
Thank You !

Below is the code that finally worked!


use <yourdatabasename>

DECLARE @TheTableName VARCHAR(100) -- Table name  

DECLARE db_cursor CURSOR FOR  
SELECT name 
FROM [QRMAssistant].[sys].[sysobjects] 
Where xtype = 'u'

OPEN db_cursor   
FETCH NEXT FROM db_cursor INTO @TheTableName   

WHILE @@FETCH_STATUS = 0   
	BEGIN   
	 
	SELECT t.TABLE_NAME as 'Table', c.COLUMN_NAME as 'Column Name' ,c.DATA_TYPE as 'Type' ,c.CHARACTER_MAXIMUM_LENGTH as 'Length',c.NUMERIC_PRECISION as 'Numeric length',c.NUMERIC_SCALE as 'Decimals',c.DATETIME_PRECISION as 'Date/Time' 
	FROM INFORMATION_SCHEMA.Tables t
	INNER JOIN INFORMATION_SCHEMA.Columns c ON t.TABLE_CATALOG=c.TABLE_CATALOG AND t.TABLE_SCHEMA=c.TABLE_SCHEMA AND t.TABLE_NAME=c.TABLE_NAME
	WHERE t.TABLE_NAME = @TheTableName
	ORDER BY t.TABLE_NAME, c.COLUMN_NAME


	FETCH NEXT FROM db_cursor INTO @TheTableName   

	END   

CLOSE db_cursor   
DEALLOCATE db_cursor

Open in new window

Author

Commented:
Thanks!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial