T-SQL: how to iterate through all tables in the database

Alex A
Alex A used Ask the Experts™
on
Hi,

I need sample code to loop through all the tables in the database, for each table get all columns concatenated in comma-delimited varchar and same with PKs, and write CREATE PROC with SELECT statement in it.

Thank you in advance.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Database Administrator
Top Expert 2005
Commented:
Please provide an example of the output you are looking for.

Here is a start...

SELECT o.name AS TableName,
	STUFF(
	(
		SELECT ', ' + c.name
		FROM sys.columns AS c
		WHERE c.object_id = o.object_id
		ORDER BY c.column_id
		FOR XML PATH('')
	), 1, 2, '') AS ColumnList
FROM sys.objects AS o
WHERE type = 'U'

Open in new window

PortletPaulEE Topic Advisor
Most Valuable Expert 2014
Awarded 2013
Commented:
it might be easier to write the sproc without concatenation of the field names, what is it you want to achieve overall?

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