Tip

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

    Requires Free Membership to View

    Login

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 first published in July 2005

    Join the conversationComment

    Share
    Comments

      Results

      Contribute to the conversation

      All fields are required. Comments will appear at the bottom of the article.

      Disclaimer: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.

      Back to top