Problem solve Get help with specific problems with your technologies, process and projects.

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

One useful (albeit unsupported) stored procedure in SQL Server 2000 is the sp_gettypestring, which can be used to return the type string for a column in a table.

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

  • Tip: A simple stored procedure to list database objects by selected type(s)
  • Chapter Download: Stored procedure primer
  • Ask the Experts: How to use results in one stored procedure from another


  • This was last published in July 2005

    Dig Deeper on SQL Server Stored Procedures

    Start the conversation

    Send me notifications when other members comment.

    By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

    Please create a username to comment.

    -ADS BY GOOGLE

    SearchBusinessAnalytics

    SearchDataCenter

    SearchDataManagement

    SearchAWS

    SearchOracle

    SearchContentManagement

    SearchWindowsServer

    Close