Technical Article

Display the SELECT statement for any table

,

Found this lovely T-SQL script to display the SELECT statement for any table.

Please replace the "'TABLE_NAME" in the script with your Table Name.

E&OE - Other variations of this script might be existing too.

-----------------------------------------------
--Display the SELECT Statement for any Table--
-----------------------------------------------
DECLARE @mStrColumns VARCHAR(8000) , @mStrTable VARCHAR(100)
SET @mStrTable ='TABLE_NAME'
--
SELECT @mStrColumns = COALESCE(@mStrColumns + ', ', '') + '[' + CAST(SC.[NAME] AS VARCHAR(100) ) +']' 
FROM [SYSCOLUMNS] SC 
JOIN [SYSOBJECTS] SO ON SC.ID = SO.ID 
WHERE SC.STATUS <> COALESCE (NULL  ,SC.STATUS+1) AND SO.NAME = @mStrTable 
--
SELECT 'SELECT ' + @mStrColumns + ' FROM '  + @mStrTable RESULTS

----------                              
--- OR ---
----------

You can try the following if you want the Schema name to be included too.

DECLARE @mStrColumns VARCHAR(8000) , @mStrTable VARCHAR(100)
SET @mStrTable ='TABLE_NAME'
--
SELECT @mStrColumns = COALESCE(@mStrColumns + ', ', '') + '[' + CAST(SC.[NAME] AS VARCHAR(100) ) +']' ,
@mStrTable = schema_Name(schema_id) + '.' + SO.NAME
FROM [SYSCOLUMNS] SC 
JOIN SYS.OBJECTS SO ON SC.ID = SO.object_ID 
WHERE SC.STATUS <> COALESCE (NULL  ,SC.STATUS+1) AND SO.NAME = @mStrTable 
--
SELECT 'SELECT ' + @mStrColumns + ' FROM '  + @mStrTable RESULTS

Please replace the "'TABLE_NAME" in the script with your Table Name.

Rate

1.71 (7)

Share

Share

Rate

1.71 (7)