How to create implicit select statements 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
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, &lt; 8K, for the PRINT portion of this to work.)