Entries Tagged as SQL

How To Select All Columns From a Table In MSQL

Posted by Paul Alkema | Tags: SQL

On occasions I've needed to perform actions on a table to all columns but wanted it to be dynamic in a way that I could use it on multiple table. In order to do so, I needed a way to pull all of the columns in my table before performing my action.

Here is an example of how to pull all of the columns in the specified table.

EXAMPLE

      DECLARE @table varchar(40)
            SET @table = 'your-table'
            
            SELECT [name]
            FROM   syscolumns
            WHERE  [id] IN
                   (SELECT [id]
                   FROM    sysobjects
                   WHERE   [name] = @table
                   )

This is how you can use this method to pull the primary key.

EXAMPLE

    DECLARE @table_name VARCHAR(40)
            SET @table = 'your-table'
            
            SELECT [name]
            FROM   syscolumns
            WHERE  [id] IN
                   (SELECT [id]
                   FROM    sysobjects
                   WHERE   [name] = @table_name
                   )
               AND colid IN
                   (SELECT SIK.colid
                   FROM    sysindexkeys SIK
                           JOIN sysobjects SO
                           ON      SIK.[id] = SO.[id]
                   WHERE   SIK.indid        = 1
                       AND SO.[name]        = @table
                   )

No Comments | April 20, 2010