Useful system stored procedures in SQL Server
Solution 1
Alt + F1 is a good shortcut key for sp_help
.
sp_helptext
is another goodie for getting stored procedure text.
Solution 2
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
Solution 3
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
Solution 4
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.
Solution 5
DotnetDude
Updated on July 19, 2020Comments
-
DotnetDude almost 4 years
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?