Useful system stored procedures in SQL Server

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?

From stackoverflow

• sp_who/sp_who2 - lets you know who is doing what on the server.

• Alt + F1 is a good shortcut key for sp_help :)

  sp_helptext is another goodie for getting stored proc text..

  DotnetDude : Nice.. Can I use Alt + F1 on a particular table similar to sp_help myTable?

  Gulzar : yep. just click Alt+F1 on a table name in the editor..
• • sp_who
  • sp_who2

  • sp_helptext

  • list of useful system stored procedures

• 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

• sp_spaceused, you can use it 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

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

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

• sp_rename

  for renaming database objects (tables, columns, indexes, etc.)

• sp_ helpindex [table] - shows you index info (same info as sp_help)

  sp_helpconstraint [table] - shows you primary/foreign key/defaults and other constraints *

  sp_depends [obj] - shows dependencies of an object, for example:

  sp_depends [table] - shows you what stored procs, views, triggers, UDF affect this table

  sp_depends [sproc] - shows what tables etc are affected/used by this stored proc

• 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.

• highlight any proc or other system object name in your query editor and hit shift-f1 to get help for that word.