Query to list all stored procedures
As Mike stated, the best way is to use information_schema
. As long as you're not in the master database, system stored procedures won't be returned.
SELECT *
FROM DatabaseName.INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_TYPE = 'PROCEDURE'
If for some reason you had non-system stored procedures in the master database, you could use the query (this will filter out MOST system stored procedures):
SELECT *
FROM [master].INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_TYPE = 'PROCEDURE'
AND LEFT(ROUTINE_NAME, 3) NOT IN ('sp_', 'xp_', 'ms_')
How to identify all stored procedures referring a particular table
SELECT Name
FROM sys.procedures
WHERE OBJECT_DEFINITION(OBJECT_ID) LIKE '%TableNameOrWhatever%'
BTW -- here is a handy resource for this type of question: Querying the SQL Server System Catalog FAQ
Check if stored procedure is running
You might query sys.dm_exec_requests
which will provide sesion_ID, waittime and futher rows of interest and CROSS APPLY sys.dm_exec_sql_text
filtering your query with the SQL for your procedure.
Select * from
(
SELECT * FROM sys.dm_exec_requests
where sql_handle is not null
) a
CROSS APPLY sys.dm_exec_sql_text(a.sql_handle) t
where t.text like 'CREATE PROCEDURE dbo.sp_sleeping_beauty%'
A reliable way to verify T-SQL stored procedures
You can choose different ways. First of all SQL SERVER 2008 supports dependencies which exist in DB inclusive dependencies of STORED PROCEDURE (see http://msdn.microsoft.com/en-us/library/bb677214%28v=SQL.100%29.aspx, http://msdn.microsoft.com/en-us/library/ms345449.aspx and http://msdn.microsoft.com/en-us/library/cc879246.aspx). You can use sys.sql_expression_dependencies and sys.dm_sql_referenced_entities to see and verify there.
But the most simple way to do verification of all STORED PROCEDURE is following:
- export all STORED PROCEDURE
- drop old existing STORED PROCEDURE
- import just exported STORED PROCEDURE.
If you upgrade DB the existing Stored Procedure will be not verified, but if you create a new one, the procedure will be verified. So after exporting and exporting of all Stored Procedure you receive all existing error reported.
You can also see and export the code of a Stored Procedure with a code like following
SELECT definition
FROM sys.sql_modules
WHERE object_id = (OBJECT_ID(N'spMyStoredProcedure'))
UPDATED: To see objects (like tables and views) referenced by Stored Procedure spMyStoredProcedure you can use following:
SELECT OBJECT_NAME(referencing_id) AS referencing_entity_name
,referenced_server_name AS server_name
,referenced_database_name AS database_name
,referenced_schema_name AS schema_name
, referenced_entity_name
FROM sys.sql_expression_dependencies
WHERE referencing_id = OBJECT_ID(N'spMyStoredProcedure');
UPDATED 2: In the comment to my answer Martin Smith suggested to use sys.sp_refreshsqlmodule
instead of recreating a Stored Procedure. So with the code
SELECT 'EXEC sys.sp_refreshsqlmodule ''' + OBJECT_SCHEMA_NAME(object_id) +
'.' + name + '''' FROM sys.objects WHERE type in (N'P', N'PC')
one receive a script, which can be used for verifying of Stored Procedure dependencies. The output will look like following (example with AdventureWorks2008):
EXEC sys.sp_refreshsqlmodule 'dbo.uspGetManagerEmployees'
EXEC sys.sp_refreshsqlmodule 'dbo.uspGetWhereUsedProductID'
EXEC sys.sp_refreshsqlmodule 'dbo.uspPrintError'
EXEC sys.sp_refreshsqlmodule 'HumanResources.uspUpdateEmployeeHireInfo'
EXEC sys.sp_refreshsqlmodule 'dbo.uspLogError'
EXEC sys.sp_refreshsqlmodule 'HumanResources.uspUpdateEmployeeLogin'
EXEC sys.sp_refreshsqlmodule 'HumanResources.uspUpdateEmployeePersonalInfo'
EXEC sys.sp_refreshsqlmodule 'dbo.uspSearchCandidateResumes'
EXEC sys.sp_refreshsqlmodule 'dbo.uspGetBillOfMaterials'
EXEC sys.sp_refreshsqlmodule 'dbo.uspGetEmployeeManagers'
Find all stored procedures that reference a specific column in some table
One option is to create a script file.
Right click on the database -> Tasks -> Generate Scripts
Then you can select all the stored procedures and generate the script with all the sps. So you can find the reference from there.
Or
-- Search in All Objects
SELECT OBJECT_NAME(OBJECT_ID),
definition
FROM sys.sql_modules
WHERE definition LIKE '%' + 'CreatedDate' + '%'
GO
-- Search in Stored Procedure Only
SELECT DISTINCT OBJECT_NAME(OBJECT_ID),
object_definition(OBJECT_ID)
FROM sys.Procedures
WHERE object_definition(OBJECT_ID) LIKE '%' + 'CreatedDate' + '%'
GO
Source SQL SERVER – Find Column Used in Stored Procedure – Search Stored Procedure for Column Name
Search text in stored procedure in SQL Server
Escape the square brackets:
...
WHERE m.definition Like '%\[ABD\]%' ESCAPE '\'
Then the square brackets will be treated as a string literals not as wild cards.
How to find out which stored procedure is using the specific column of a specific table in SQL Server?
You don't need to use sys.sql_dependencies
which has been deprecated, you can use the newer sys.dm_sql_referenced_entities
instead.
Unfortunately, after some testing, it turns out that
sys.sql_expression_dependencies
andsys.dm_sql_referencing_entities
do not return information on column-level dependencies.
SELECT OBJECT_SCHEMA_NAME(o.object_id),
OBJECT_NAME(o.object_id)
FROM sys.objects o
JOIN sys.schemas s ON s.schema_id = o.schema_id
CROSS APPLY sys.dm_sql_referenced_entities(QUOTENAME(s.name) + '.' + QUOTENAME(o.name), 'OBJECT') d
INNER JOIN sys.columns c
ON c.object_id = d.referenced_id
AND c.column_id = d.referenced_minor_id
AND c.object_id = OBJECT_ID('dbo.Customers')
AND c.name = 'FullName'
WHERE o.type IN ('FN','TF','IF','P','V','TR'); -- scalar, multi-line and inline funcs, procs, views, triggers
Related Topics
Update Xml Tag in a Clob Column in Oracle
Ms Access Query to SQL Server - Distinctrow
Find Groups with Matching Rows
Full-Text Search SQL Server 2005
Informix 7.3 Isql Insert Statement - Text/Blob/Clob Field Insert Error
Finding All Children in a Hierarchy SQL
Ukrainian Character Change to Question Mark When Insert to Table
Join Four Tables Involving Left Join Without Duplicates
Best Way to Reset an Oracle Sequence to the Next Value in an Existing Column
SQL Queries on String Columns - Sorting According to Language
How to Get Use Text Columns in a Trigger
Get Data Type of Field in Select Statement in Oracle
Oracle Insert via Select from Multiple Tables Where One Table May Not Have a Row
Convert Unknown Number of Comma Separated Varchars Within 1 Column into Multiple Columns
Jooq - Support for Update ... Set ... Query with Arbitrary Degree