Display the SELECT statement for any table

  • Comments posted to this topic are about the item Display the SELECT statement for any table

    Mehernosh.

  • You should add the schema of the table. That would be better.

  • Fair point HextallFanForLife.

    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) + '.'TABLE_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.

    Hope this helps 🙂

    Mehernosh.

  • Mehernosh (8/13/2013)


    Fair point HextallFanForLife.

    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) + '.'TABLE_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.

    Hope this helps 🙂

    I believe that you want the SELECT statement in the above script to read as follows:

    SELECT @mStrColumns = COALESCE(@mStrColumns + ', ', '') + '[' + CAST(SC.[NAME] AS VARCHAR(100) ) +']' ,@mStrTable = schema_Name(schema_id) + '.' + 'TABLE_NAME'

    Otherwise, there is an error and the command does not execute.

    Of course, as the author explains, you should replace TABLE_NAME with the actual name of the table you're after.

  • I got a syntax error from the text on the second example.

    Change the line

    SELECT @mStrColumns = COALESCE(@mStrColumns + ', ', '') + '[' + CAST(SC.[NAME] AS VARCHAR(100) ) +']' ,@mStrTable = schema_Name(schema_id) + '.'TABLE_NAME'

    To:

    SELECT @mStrColumns = COALESCE(@mStrColumns + ', ', '') + '[' + CAST(SC.[NAME] AS VARCHAR(100) ) +']' ,@mStrTable = schema_Name(schema_id) + '.' + @mStrTable

    Tom in Sacramento For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/

  • This is just what I need to keep me from using select * when doing a quick extract that sometimes ends up being used repeatedly.

  • Why don't you just right click on the table name in SSMS in the object explorer and do 'Script table as' then select 'SELECT to'. This will do the same thing plus format the results.

    Am I missing something?

    -------------------------------------------------------------
    we travel not to escape life but for life not to escape us
    Don't fear failure, fear regret.

  • Thanks Ken Hedges

    ---------------------------------

    ---Complete working example---

    ---Apologies for any mix ups.

    ---------------------------------

    --Create a test table & insert test record.

    Create Table TestSelect (tstField1 Varchar(10), tstField2 Varchar(10), tstField3 Varchar(10))

    Insert into TestSelect Select 'A', 'A1', 'A100'

    --Cheking the Table

    SELECT * FROM TESTSELECT

    ---------------------------------

    ----Main query----

    ---------------------------------

    DECLARE @mStrColumns VARCHAR(8000) , @mStrTable VARCHAR(100)

    SET @mStrTable ='testSelect'

    --

    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

    ---The results as expected.

    --SELECT [tstField1], [tstField2], [tstField3] FROM dbo.TestSelect

    --Remember to drop the table afterwards.

    Drop table testSelect

    Hope this helps.

    Mehernosh.

  • Tom_Sacramento

    ---------------------------------

    ---Complete working example---

    ---Apologies for any mix ups.

    ---------------------------------

    --Create a test table & insert test record.

    Create Table TestSelect (tstField1 Varchar(10), tstField2 Varchar(10), tstField3 Varchar(10))

    Insert into TestSelect Select 'A', 'A1', 'A100'

    --Cheking the Table

    SELECT * FROM TESTSELECT

    ---------------------------------

    ----Main query----

    ---------------------------------

    DECLARE @mStrColumns VARCHAR(8000) , @mStrTable VARCHAR(100)

    SET @mStrTable ='testSelect'

    --

    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

    ---The results as expected.

    --SELECT [tstField1], [tstField2], [tstField3] FROM dbo.TestSelect

    --Remember to drop the table afterwards.

    Drop table testSelect

    Hope this helps.

    Mehernosh.

  • below86

    below86

    Why don't you just right click on the table name in SSMS in the object explorer and do 'Script table as' then select 'SELECT to'. This will do the same thing plus format the results.

    Am I missing something?

    Perfectly fine and valid. You are not missing anything out.

    Just wanted a SQL script to do the needful - hence the post.

    Mehernosh.

  • Thank you for the script. I have updated this to a stored procedure that I can pass in the table name:

    CREATE PROCEDURE dbo.UTIL_DynamicTable_SELECT

    @mStrTable varchar(255)

    AS

    -----------------------------------------------

    --Display the SELECT Statement for any Table--

    -----------------------------------------------

    DECLARE @mStrColumns VARCHAR(8000)

    , @vSQLString nvarchar(max)

    , @ParmDefinition nvarchar(500)

    , @SQLResults AS NVARCHAR(250)

    BEGIN

    SELECT @mStrColumns = COALESCE(@mStrColumns + ', ', '') + '[' + CAST(SC.[NAME] AS VARCHAR(100) ) +']'

    FROM [SYSCOLUMNS] SC INNER JOIN [SYSOBJECTS] SO ON SC.ID = SO.ID

    WHERE SC.STATUS <> COALESCE (NULL ,SC.STATUS+1) AND SO.NAME = @mStrTable

    SELECT @vSQLString = 'SELECT ' + @mStrColumns + ' FROM ' + @mStrTable

    SET @ParmDefinition = N'@SQLResultsOut NVARCHAR(max) OUTPUT';

    exec sp_executesql @vSQLString, @ParmDefinition,@SQLResultsOut = @SQLResults OUTPUT

    END

    Owen White

  • @below86

    I guess the real draw would be if you wanted to automate a series of events. I'm probably not coming up with the best example but maybe using this to generate dynamic SQL and bolting on a few extra statements to SELECT INTO.

    SET NOCOUNT ON

    DECLARE @schemaname VARCHAR(128),

    @objname VARCHAR(128)

    SELECT @schemaname = 'dbo',

    @objname = 'ServerList'

    SELECT

    (SELECT 'SELECT '

    + STUFF((

    SELECT ',' + QUOTENAME(sc.[name])

    FROM [sys].[syscolumns] sc

    INNER JOIN sys.objects so

    ON sc.[id] = so.[object_id]

    WHERE so.[object_id] = OBJECT_ID(@objname)

    AND so.[schema_id] = SCHEMA_ID(@schemaname)

    ORDER BY sc.[colid]

    FOR XML PATH('')), 1, 1, '')

    )

    + (SELECT ' INTO '

    + QUOTENAME(@schemaname)

    + '.'

    + QUOTENAME(@objname

    + '_'

    + CONVERT(CHAR(8), GETDATE(), 112))

    + ' FROM '

    + QUOTENAME(@schemaname)

    + '.'

    + QUOTENAME(@objname)

    + ';'

    )

    SET NOCOUNT OFF

    GO

  • Thanks 'You name Here', its been a good day for gaining new knowledge. Not real sure where or when I would need to do this but I will try and remember this.

    Thanks,

    -------------------------------------------------------------
    we travel not to escape life but for life not to escape us
    Don't fear failure, fear regret.

  • That's great Owen White .

    Thank you for the script. I have updated this to a stored procedure that I can pass in the table name:

    CREATE PROCEDURE dbo.UTIL_DynamicTable_SELECT

    @mStrTable varchar(255)

    AS

    -----------------------------------------------

    --Display the SELECT Statement for any Table--

    -----------------------------------------------

    DECLARE @mStrColumns VARCHAR(8000)

    , @vSQLString nvarchar(max)

    , @ParmDefinition nvarchar(500)

    , @SQLResults AS NVARCHAR(250)

    BEGIN

    SELECT @mStrColumns = COALESCE(@mStrColumns + ', ', '') + '[' + CAST(SC.[NAME] AS VARCHAR(100) ) +']'

    FROM [SYSCOLUMNS] SC INNER JOIN [SYSOBJECTS] SO ON SC.ID = SO.ID

    WHERE SC.STATUS <> COALESCE (NULL ,SC.STATUS+1) AND SO.NAME = @mStrTable

    SELECT @vSQLString = 'SELECT ' + @mStrColumns + ' FROM ' + @mStrTable

    SET @ParmDefinition = N'@SQLResultsOut NVARCHAR(max) OUTPUT';

    exec sp_executesql @vSQLString, @ParmDefinition,@SQLResultsOut = @SQLResults OUTPUT

    END

    Owen White

    Nicely done.

    Mehernosh.

  • Hey "Your Name Here" thanks for the example.

    I guess the real draw would be if you wanted to automate a series of events. I'm probably not coming up with the best example but maybe using this to generate dynamic SQL and bolting on a few extra statements to SELECT INTO.

    We can use a bit of Dynamic SQL or create it as a SP or UDF or .... the possibilities are there for us to explore.

    🙂

    Great stuff.

    Mehernosh.

Viewing 15 posts - 1 through 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply