Looping code through tables in a SQl Server database

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
Lenny GrayAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Kyle AbrahamsSenior .Net DeveloperCommented:
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.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Lenny GrayAuthor 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

0
Lenny GrayAuthor Commented:
Thanks!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.