Undocumented stored procedure: Return string type for a column with sp_gettypestring

SQL Server 2000 has a number of useful albeit undocumented (and therefore unsupported) stored procedures. Among them is the sp_gettypestring stored procedure, which can be used to return the type string for a column in a table. If you're trying to use a program to determine what a column is -- either in the context of a stored procedure or another piece of code -- this is one way to do it.

sp_gettypestring uses this syntax:

sp_gettypestring <tabid>, <colid>, <typestring>

<tabid> and <colid> are integers that refer to a table and column ID respectively. <typestring> is the output parameter (nvarchar(255)). Because you can't pass a column or table name to sp_gettypestring, you may need to derive this information programmatically.

Here's an example of how to do this:

USE MyDatabase

GO

DECLARE @intTableID int, @strTypeString nvarchar(255)

SELECT @intTableID = object_id('MyTable')

EXEC sp_gettypestring @intTableID, 1, @strTypeString output

SELECT @strTypeString

GO

In this example, sp_gettypestring is used on a database named MyDatabase, with a table named MyTable. The "1" in the EXEC sp_gettypestring parameter list means "Obtain the type string for the first column in the table." If you wanted the fourth column, you'd use "4," and so on.

The SELECT @intTableID statement uses the T-SQL object_id function to determine what the table ID number is for MyTable. If you already know the table ID, you can pass this manually, but it's usually painless enough to derive it in this fashion.

Bear in mind that because this is an undocumented procedure, there's no guarantee it will be supported in future versions of SQL Server or even in future updates of SQL Server 2000. For now, however, it's a convenient way to derive this sort of information without too much extra work.

About the author: Serdar Yegulalp is editor of the Windows Power Users Newsletter. Check it out for the latest advice and musings on the world of Windows network administrators -- and please share your thoughts as well!

More information from SearchSQLServer.com