Site sponsored by: Idera Try Idera’s new SQL admin toolset
SQL Server Performance

  • Home
  • Articles
  • Forums
  • Tips
  • FAQ's
  • Blogs
  • Software
  • Books
  • About Us
RSS Feeds
Sign in | Join


Article Topics

All Articles
Peformance Tuning
Audit
Business Intelligence
Clustering
Reporting Services
Developer
General DBA
ASP.NET / ADO.NET

SQL Server 2008 - Worth the Wait

SQL Server’s first significant upgrade in three years features a number of envelope-pushing enhancements and improvements. Which will have the greatest impact on SQL administration and development? More...
Latest Articles

Slowly Changing Dimensions in SQL Server 2005
Audit Data Modifications
SQL Server 2008’s Management Data Warehouse
Same Report but Different Methods in SQL Server Reporting Services ...

More     
 
Latest FAQ's

SSIS Lookups are Case Sensitive
Convert Number to Words in SSRS
After installing SP2 on SQL Server 2005 x64, when trying to ...
Remote Name Could not be Resolved in SQL Server Reporting Services ...

More     
   
Latest Software Reviews

SQL Server DBA Dashboard
SwisSQL DBChangeManager
SQLMesh - SQL Server Search Tool
SoftTreeTech SQL Assistant

More     

articles >> general dba >> SQL Server 2000 Undocumented Stored Procedures ...

SQL Server 2000 Undocumented Stored Procedures

By : Alexander Chigrik
May 01, 2003
Printer friendly



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)

 


    Next Page>>    








Home | Peformance Articles | Audit Articles | Business Intelligence Articles | Clustering Articles | Developer Articles | Reporting Services Articles | DBA Articles | ASP.NET / ADO.NET Articles | DBA FAQ's | Developer Peformance FAQ's | DBA Peformance FAQ's | Developer FAQ's | Clustering FAQ's | Error Messages | Audit Tool Reviews | Backup Tool Reviews | Coding Tool Reviews | Compare Tool Reviews | Documentation Tool Reviews | Design Tool Reviews | Monitoring Tool Reviews | Log Tool Reviews | Reporting Tool Reviews | Clustering Tool Reviews | Security Tool Reviews | Change Management Tool Reviews | Remote Access Tool Reviews | Book Reviews | Security Tool Reviews | QDPMA Performance Tuning | ADO.NET / ASP.NET | Administration | Analysis/OLAP Services | Application Development | Configuration | Components | ETL | Hardware | High Availability | Hints | Index | Misc | Operating Systems | Performance Tuning | Replication | T-SQL | Views