Useful system stored procedures in SQL Server

Question

I recently discovered that I could use the sp_help to get a table definition and have been hooked onto it since then. Before my discovery, I had to open up the Object explorer in SQL Management studio, manually search for the table name, right click on the table and select Design. That was a lot of effort!

What other system stored procedures do you all use that you can't simply live without?

Answer 1

sp_catalogs 
sp_column_privileges
sp_column_privileges_ex
sp_columns
sp_columns_ex
sp_databases
sp_cursor
sp_cursorclose
sp_cursorexecute
sp_cursorfetch
sp_cursoroption
sp_cursoropen
sp_cursorprepare
sp_cursorprepexec
sp_cursorunprepare
sp_execute
sp_datatype_info
sp_fkeys
sp_foreignkeys
sp_indexes
sp_pkeys
sp_primarykeys
sp_prepare
sp_prepexec
sp_prepexecrpc
sp_unprepare
sp_server_info
sp_special_columns
sp_sproc_columns
sp_statistics
sp_table_privileges
sp_table_privileges_ex
sp_tables
sp_tables_ex 

Check This link also

http://msdn.microsoft.com/en-us/library/ms176007.aspx

Answer 2

You can use sp_spaceused to determine the size of a table or the entire database. If you pass the table name, it returns the space used for that table, when called with no argument it gives the space of the database.

Answer 3

Select * From sysobjects where xtype='U' order by Name

Gives a list of all user-defined tables in a database.

Answer 4

master.dbo.xp_cmdshell

I can't list the number of times I didn't have RDP access to a box but did have a SQL login with sufficient permissions to execute that in order to run shell commands on it.

Answer 5

All of these undocumented ones

xp_getnetname 

xp_fileexist 

xp_dirtree 

xp_subdirs 

sp_who2

xp_getfiledetails 

xp_fixeddrives 

Sp_tempdbspace 

xp_enumdsn 

xp_enumerrorlogs

sp_MSforeachtable 

sp_MSforeachDB 

See here: Undocumented stored procedures

And now since SQl Server 2005 all the Dynamic Management Views like sys.dm_db_index_usage_stats

Answer 6

• sp_who
• sp_who2

• sp_helptext

• list of useful system stored procedures