In this article, I want to tell you about some useful undocumented stored procedures shipped with SQL Server 2000. sp_MSget_qualified_name
This stored procedure is used to get the qualified name for the given object id.
Syntax
| sp_MSget_qualified_name object_id, qualified_name |
where
object_id – is the object id. object_id is int. qualified_name – is the qualified name of the object. qualified_name is nvarchar(512).
This is an example to get the qualified name for the authors table from the pubs database.
| USE pubs
GO DECLARE @object_id int, @qualified_name nvarchar(512) SELECT @object_id = object_id(‘authors’) EXEC sp_MSget_qualified_name @object_id, @qualified_name OUTPUT SELECT @qualified_name GO |
Here is the result set from my machine:
| ————————————–
[dbo].[authors] |
sp_MSdrop_object
This stored procedure is used to drop an object (it can be table, view, stored procedure or trigger) for the given object id, object name, and object owner. If object id is provided, then the object name and the object owner need not be specified. Syntax
| sp_MSdrop_object [object_id] [,object_name] [,object_owner] |
where
object_id – is the object id. object_id is int, with a default of NULL.
object_name – is the name of the object. object_name is sysname, with a default of NULL.
object_owner – is the object owner. object_owner is sysname, with a default of NULL.
This is the example of dropping the titleauthor table from the pubs database.
| USE pubs
GO DECLARE @object_id int SELECT @object_id = object_id(‘titleauthor’) EXEC sp_MSdrop_object @object_id GO |
sp_gettypestring
This stored procedure returns the type of string for the given table id and column id.
Syntax
| sp_gettypestring tabid, colid, typestring |
where
tabid – is the table id. tabid is int.
colid – is the column id. colid is int.
typestring – is the type string. It’s an output parameter. typestring is nvarchar(255)
This is the example to get the type string for the column number 2 in the authors table, from the pubs database.
| USE pubs
GO DECLARE @tabid int, @typestring nvarchar(255) SELECT @tabid = object_id(‘authors’) EXEC sp_gettypestring @tabid, 2, @typestring output SELECT @typestring GO |
Here is the result set from my machine:
| ——————————-
varchar(40) |