How to list column headers of a SQL Server table using sp_help perhaps?

Question

Hi,

I have a few tables with 70-80 columns in them. I would like to populate them with somewhat random data, unless I will not be able to do so due to key violation, etc.

The first step would be simply to get the list of all headers. There seem to be two ways:

A) Run select * from table_of_interest; in MSFT SQL Server Management Studio 2008. Now, right-click the result and click "Copy With headers". However, I get zero rows back, and when I try to copy nothing + headers, I get:

TITLE: Microsoft SQL Server Management Studio
------------------------------

Value cannot be null.
Parameter name: data (System.Windows.Forms)

------------------------------
BUTTONS:

OK
------------------------------

This looks like a bug ... anyhow ... there is another way.

B) I can run sp_help table_of_interest;. However, I end up getting too much back. I get 7 different tables back but I am only interested in the second one. The columns of the second table are:

Column_name | Type | Computed | Length | Prec | Scale | Nullable | 
TrimTrailingBlanks | FixedLenNullInSource | Collation

I might be interested in just a Column_name and Type, but maybe other columns.

So ... since sp_help probably runs a bunch of queries ... how do I get under the hood? How can I run the second query AND filter down the number of columns that I am interested in?

Many Thanks!

Answer 1

try this:

select
    c.COLUMN_NAME ,c.DATA_TYPE ,c.CHARACTER_MAXIMUM_LENGTH,c.NUMERIC_PRECISION,c.NUMERIC_PRECISION_RADIX ,c.NUMERIC_PRECISION_RADIX,c.NUMERIC_SCALE,c.DATETIME_PRECISION 
        --t.TABLE_CATALOG,t.TABLE_SCHEMA,t.TABLE_NAME
    from INFORMATION_SCHEMA.Tables            t
        INNER JOIN INFORMATION_SCHEMA.Columns c ON t.TABLE_CATALOG=c.TABLE_CATALOG AND t.TABLE_SCHEMA=c.TABLE_SCHEMA AND t.TABLE_NAME=c.TABLE_NAME
    WHERE t.TABLE_NAME='YourTableName' --<<<<
    ORDER BY --t.TABLE_CATALOG,t.TABLE_SCHEMA,t.TABLE_NAME,
        c.ORDINAL_POSITION

I'm not sure if you want the data type of not, and if you want just one table or all, you can modify as necessary.

EDIT after OP's comment:

here's the procedure, if you need table_catalog and table_schema as parameter uncomment as necessary:

CREATE PROCEDURE GetColumnNames
(
    --@TABLE_CATALOG nvarchar(128),
    --@TABLE_SCHEMA  nvarchar(128),
    @TableName   sysname
)
AS
select
    c.COLUMN_NAME 
        ,CASE 
             WHEN DATA_TYPE IN ('int','smalldatetime','datetime','smallint','bigint')THEN DATA_TYPE
             WHEN DATA_TYPE='char' THEN 'char('+CONVERT(varchar(5),CHARACTER_MAXIMUM_LENGTH)+')'
             WHEN DATA_TYPE='varchar' THEN 'varchar('+CONVERT(varchar(5),CHARACTER_MAXIMUM_LENGTH)+')'
             WHEN DATA_TYPE='numeric' THEN 'numeric('+CONVERT(varchar(5),NUMERIC_PRECISION)+','+CONVERT(varchar(5),NUMERIC_SCALE)+')'
             WHEN DATA_TYPE='decimal' THEN 'decimal('+CONVERT(varchar(5),NUMERIC_PRECISION)+','+CONVERT(varchar(5),NUMERIC_SCALE)+')'
             ELSE DATA_TYPE
         END AS DataType
        ,c.DATA_TYPE ,c.CHARACTER_MAXIMUM_LENGTH,c.NUMERIC_PRECISION,c.NUMERIC_PRECISION_RADIX ,c.NUMERIC_PRECISION_RADIX,c.NUMERIC_SCALE,c.DATETIME_PRECISION 
    from INFORMATION_SCHEMA.Columns c
    WHERE c.TABLE_NAME=@TableName --AND c.TABLE_CATALOG=@TABLE_CATALOG AND c.TABLE_SCHEMA=@TABLE_SCHEMA
    ORDER BY c.ORDINAL_POSITION

RETURN 0
GO

use it like this:

exec GetColumnNames 'yourtablename'

the INFORMATION_SCHEMA.Columns has several columns you might be interested in, so read the doc and modify the query to what you need.