[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1484
  • Last Modified:

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

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.
0
quasar_ee
Asked:
quasar_ee
2 Solutions
 
Brian CroweCommented:
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

0
 
PortletPaulCommented:
it might be easier to write the sproc without concatenation of the field names, what is it you want to achieve overall?
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now