Probably very simple StorProc Question

[uncLez]

Hi all,
this is probably very simple, but I just can't seem to find it in the online books and my Stored Procedure book.

In a Stored Procedure I am writing I need to use the result of the sp_columns system stored procedure. More specifically the Type_Name property.

How do I do this? How can I select just this property? I get the result set already, but how to page through it? Can I declare a cursor for this? For instance like

Declare stpcursor cursor FOR EXEC sp_columns @table_name = 'tablename', @column_name = 'columnname'

Or could I somehow retrieve te information more easily with the dbo.tablename.columnname.datatype property? That would ofcourse be easiest, but can it be done? If so, HOW???

Thanx in advance,
Anton.

 

[tlbroadbent]

Use the Information Schema views.

Select Table_Name, Column_Name, Type_name
From information_schema.tables
Where table_name='tablename' 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.

 

[ColinM]

Something like this:

select column_name,data_type from information_schema.columns