Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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?

like image 488
DotnetDude Avatar asked Feb 23 '09 17:02

DotnetDude


People also ask

What are system stored procedures in SQL Server?

SQL Server stored procedure is a batch of statements grouped as a logical unit and stored in the database. The stored procedure accepts the parameters and executes the T-SQL statements in the procedure, returns the result set if any.

What are stored procedures in SQL how they are useful?

A stored procedure is a prepared SQL code that you can save, so the code can be reused over and over again. So if you have an SQL query that you write over and over again, save it as a stored procedure, and then just call it to execute it.

What is system defined stored procedure?

System Defined Stored Procedure These are physically stored in hidden SQL Server Resource Database and logically appear in the sys schema of each user-defined and system-defined database. This procedure starts with the sp_ prefix. Hence we don't use this prefix when naming user-defined procedures.


4 Answers

Alt + F1 is a good shortcut key for sp_help.

sp_helptext is another goodie for getting stored procedure text.

like image 86
Gulzar Nazim Avatar answered Sep 22 '22 22:09

Gulzar Nazim


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

like image 38
SQLMenace Avatar answered Sep 20 '22 22:09

SQLMenace


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

like image 43
MikeW Avatar answered Sep 22 '22 22:09

MikeW


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.

like image 20
Dinesh Manne Avatar answered Sep 20 '22 22:09

Dinesh Manne