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_who/sp_who2 - lets you know who is doing what on the server.

Answer 2

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

Answer 3

sp_rename

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

Answer 4

Alt + F1 is a good shortcut key for sp_help.

sp_helptext is another goodie for getting stored procedure text.

Answer 5

I know it's a rather old topic, but it still has a high rank in search engines.

These are my favorite stored procedures, I hope you find them useful too. Almost all of them can be achieved with a simple command, but these stored procedures perform some checks and validation that is useful.

[sys].[sp_revokelogin]
[sys].[sp_password]
[sys].[sp_MSchangeobjectowner]
[sys].[sp_addlogin]
[sys].[sp_addrole]
[sys].[sp_adduser]
[sys].[sp_denylogin]
[sys].[sp_droplogin]
[sys].[sp_droprole]
[sys].[sp_droprolemember]
[sys].[sp_dropuser]

Answer 6

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