putting sp_columns results into a temp table

[roswald]

Is there a way to get the layout of a table and insert the results into a temp table?
I use this statement to get the layout to show up in Query Analyzer but I have to cut and paste into Excel to create the document.

EXEC sp_columns @table_name = 'EX_AGENT_RATE'

 

[wilk]

try this instead.

select * into temptable from information_schema.columns where table_name ='EX_AGENT_RATE'

hope it works for you

 

[AWithers]

You might need to put a # at the start of the target table name if you do not have permission to create a table (# denotes temporary table)

e.g.
select * into #temptable from information_schema.columns where table_name ='EX_AGENT_RATE'

 

[tlbroadbent]

You can insert the result of the stored procedure into a table as follows.

Create table #tmp
(TABLE_QUALIFIER varchar(40),
TABLE_OWNER varchar(20),
TABLE_NAME varchar(40),
COLUMN_NAME varchar(40),
DATA_TYPE int,
TYPE_NAME varchar(20),
PREC int, LENGTH int,
SCALE int, RADIX int,
NULLABLE char(4),
REMARKS varchar(128),
COLUMN_DEF varchar(40),
SQL_DATA_TYPE int,
SQL_DATETIME_SUB int,
CHAR_OCTET_LENGTH int,
ORDINAL_POSITION int,
IS_NULLABLE char(4),
SS_DATA_TYPE int)

Set nocount on

Insert #tmp
Exec sp_columns @table_name = 'EX_AGENT_RATE' '

Select * From #tmp

Drop table #tmp Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.