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

Posted on 2014-08-22
Last Modified: 2014-08-29

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.
Question by:quasar_ee
    LVL 34

    Accepted Solution

    Please provide an example of the output you are looking for.

    Here is a start...

    SELECT AS TableName,
    		SELECT ', ' +
    		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

    LVL 47

    Assisted Solution

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

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
    Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
    Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
    Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

    761 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    8 Experts available now in Live!

    Get 1:1 Help Now