How to create implicit select statements

Question

I need to create an implicit select statement for each table with all of its coluns - i.e. SELECT COL1, COL2, COL3 FROM TABLE1 instead of SELECT * FROM TABLE1.

There are too many tables to use the scripting feature in SMSS so I was hoping to use one of the system tables. I can query the table and column names from INFORMATION_SCHEMA.COLUMNS, but the I'd have to use a cursor to build the string as required.

Just hoping there's an easier way of doing it.

Thanks

Answer 1

Not sure that this is simpler than a cursor solution, but here is what I would do:

Before we begin we need a few tables in a SQL Fiddle:

MS SQL Server 2012 Schema Setup

CREATE TABLE dbo.tbl1(c1 INT, c2 INT, c3 INT);
CREATE TABLE dbo.tbl2(c4 INT, c5 INT, c6 INT);
CREATE TABLE dbo.tbl3(c7 INT, c8 INT, c9 INT);

First we need the list of all user tables. For that we can use the sys.tables catalog view. The OBJECT_SCHEMA_NAME() function gets us the schema name, the OBJECT_NAME() function the table name. The QUOTENAME() function quotes the names correctly, in case some of those contain special characters, key words or spaces. (In this example the use of OBJECT_NAME() is not strictly necessary as sys.tables has a name column, but I left it in as you can use this pattern with any catalog view that has an object_id column.)

Query 1:

SELECT 
  QUOTENAME(OBJECT_SCHEMA_NAME(T.object_id))+'.'+
  QUOTENAME(OBJECT_NAME(T.object_id)) AS quoted_table_name,
  T.object_id
FROM sys.tables AS T;

Results:

| QUOTED_TABLE_NAME | OBJECT_ID |
|-------------------|-----------|
|      [dbo].[tbl1] | 245575913 |
|      [dbo].[tbl2] | 261575970 |
|      [dbo].[tbl3] | 277576027 |

The next step is to get the list of column names, again quoted. We can use the sys.columns catalog view for that.

Query 2:

SELECT C.name,C.column_id 
  FROM sys.columns AS C
 WHERE C.object_id = OBJECT_ID('dbo.tbl1');

Results:

| NAME | COLUMN_ID |
|------|-----------|
|   c1 |         1 |
|   c2 |         2 |
|   c3 |         3 |

The next hurdle is to get those columns in a comma separated list. There is no string concatenation aggregate function build in so we have to use a trick:

Query 3:

SELECT STUFF((
  SELECT ','+QUOTENAME(name)
    FROM sys.columns AS C
   WHERE C.object_id = OBJECT_ID('dbo.tbl1')
   ORDER BY C.column_id
     FOR XML PATH(''),TYPE
  ).value('.','NVARCHAR(MAX)'),1,1,'') AS clomun_list;

Results:

|    CLOMUN_LIST |
|----------------|
| [c1],[c2],[c3] |

With that all pieces are in place and we just have to put them all together:

Query 4:

SELECT 'SELECT ' +
       CL.column_list +
       ' FROM ' +
       QUOTENAME(OBJECT_SCHEMA_NAME(T.object_id)) + '.' +
       QUOTENAME(OBJECT_NAME(T.object_id)) +
       ';' AS select_statement
FROM sys.tables AS T
CROSS APPLY (
  SELECT STUFF((
    SELECT ','+QUOTENAME(name)
      FROM sys.columns AS C
     WHERE C.object_id = T.object_id
     ORDER BY C.column_id
       FOR XML PATH(''),TYPE
    ).value('.','NVARCHAR(MAX)'),1,1,'') AS column_list
  )CL;

Results:

|                         SELECT_STATEMENT |
|------------------------------------------|
| SELECT [c1],[c2],[c3] FROM [dbo].[tbl1]; |
| SELECT [c4],[c5],[c6] FROM [dbo].[tbl2]; |
| SELECT [c7],[c8],[c9] FROM [dbo].[tbl3]; |

This example will work in SQL 2005 and later, assuming you are on the latest service pack. There is no clean solution to achieve this with SQL Server 2000. In that case you need to go back to your cursor solution.

Answer 2

This will work on 2005+ provided you don't have any columns or objects with apostrophes in them - if so you'll need to do some extra massaging to the output. Also, please don't use INFORMATION_SCHEMA unless you absolutely have to...

DECLARE @sql nvarchar(max); SET @sql = N'';

SELECT @sql = @sql + N'PRINT ''SELECT ' + STUFF((SELECT ',' + QUOTENAME(name)
    FROM sys.columns AS c WHERE [object_id] = t.[object_id]
    ORDER BY column_id FOR XML PATH, 
    TYPE).value('.[1]', 'nvarchar(max)'),1,1,'')
  + ' FROM ' + QUOTENAME(s.name) + '.' + QUOTENAME(t.name) + ';'';'
FROM sys.tables AS t
INNER JOIN sys.schemas AS s
  ON t.[schema_id] = s.[schema_id];

EXEC sys.sp_executesql @sql;

(Also, your list of columns needs to be reasonable, < 8K, for the PRINT portion of this to work.)

Answer 3

Thank you to Sebastian and Aaron. Ultimately, I went with the following:

SELECT DISTINCT
    QUOTENAME(OBJECT_SCHEMA_NAME(c.object_id)) + '.' +
    QUOTENAME(OBJECT_NAME(c.object_id)) + '.' +
    (
        SELECT STUFF((SELECT 
            ',' + QUOTENAME(c2.name)
        FROM 
            sys.columns as c2  
        WHERE 
            c2.object_id = OBJECT_ID(OBJECT_NAME(c.object_id))
        ORDER BY
            c2.column_id
        FOR XML PATH(''),TYPE).value('.','NVARCHAR(MAX)'),1,1,'') AS column_list
    )
FROM 
    sys.columns c
WHERE 
    OBJECT_SCHEMA_NAME(c.object_id) = 'dbo'