“Undocumented Extended Stored Procedures” is a dynamic link library that runs directly in the address space of SQL Server. We can create our own extended stored procedures using the C Language. Query Analyzer will help you to run the Extended Stored Procedures. Here we are going to explain some useful extended undocumented stored procedures.
Note: You should not depend on the undocumented procedures. They may be deprecated in the future, but may save you from writing additional scripts.
1. Sp_MSgetversion
This procedure will return the version of the Microsoft SQL Server that is currently running.
Use the following command:
Select @@version
This returns the current version of the SQL server and displays more information than Sp_MSgetversion.
Example
Sp_MSgetversion
Select @@version
2. Xp_getnetname
This procedure will return the network name of the server.
Example
Execute Xp_getnetname
3. Sp_who2
This procedure will return the current SQL Server procedures and users. It is similar to Sp_who, and displays some additional information.
Example
EXEC sp_who2
4. xp_dirtree
This extended stored procedure is used to list all of the subdirectory names in the input directory.
Example
EXEC master..xp_dirtree 'C:\'
5. xp_enum_oledb_providers
This procedure will display all the OLE DB providers. It also displays the provider name, Parse Name and provider descriptions.
Example
xp_enum_oledb_providers
6. xp_fileexist
This will return 0 or 1. If the file exists it will display 1, if not it will display 0.
Example
EXEC master..xp_fileexist 'C:\ASIDocs\Test.doc'
7. master..xp_subdirs
This procedure will display the input directory’s immediate sub directory.
Example
EXEC master..xp_subdirs 'C:\'
8. sp_MShelpcolumns
This procedure returns the complete schema of a table, including name, type and length. We have to give the table name as an input parameter.
Example
EXEC sp_MShelpcolumns 'tbltmpimportc'
9. sp_MShelpindex
This stored procedure returns the key name, status, index column names and the used segments of the given table. We have to specify the table name as a parameter.
Example
EXEC sp_MShelpindex 'tblorder'
10. xp_fixeddrives
This procedure will display all of the fixed drives in the system and the free space on the machine
Example
EXEC master..xp_fixeddrives
11. xp_enumerrorlogs
This procedure will return all of the error logs with the last changed date.
Example
EXEC master..xp_enumerrorlogs
12. xp_enumgroups
This stored procedure returns the list of Windows NT groups and their description.
EXEC master..xp_enumgroups
13. sp_MStablerefs
This procedure will return all the dependencies for the table. We have to give the table name as an input parameter.
EXEC sp_MStablerefs 'tblpipes'
14. sp_MSforeachtable
This procedure will disable or Enable all of the triggers in a database.
Example
Use EXEC sp_MSforeachtable 'ALTER TABLE ? DISABLE TRIGGER ALL'
The above command will disable all of the Triggers in the current database.
EXEC sp_MSforeachtable 'ALTER TABLE ? ENABLE TRIGGER ALL'
The above command will enable all of the Triggers in the current database.
15. Delete all the data from all the tables in your database
In order to delete all of the rows from the database, the following steps should be considered.
Disable the referential integrity
Delete Rows
Enable referential integrity
Example
-- disable referential integrity
EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'
GO
EXEC sp_MSForEachTable '
IF OBJECTPROPERTY(object_id(''?''), ''TableHasForeignRef'') = 1
DELETE FROM ?
else
TRUNCATE TABLE ? '
GO
-- enable referential integrity again
EXEC sp_MSForEachTable 'ALTER TABLE ? CHECK CONSTRAINT ALL'
GO
16. RESEED all tables to 0
The following script will help you to reseed all tables to 0.
Example
EXEC sp_MSForEachTable '
IF OBJECTPROPERTY(object_id(''?''), ''TableHasIdentity'') = 1
DBCC CHECKIDENT (''?'', RESEED, 0)'
GO