List of Stored Procedure from Table

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_')

Find the stored procedure which uses a particular table in sql server

The following query maybe of help to you.

SELECT OBJECT_NAME(id)
FROM SYSCOMMENTS S
INNER JOIN SYS.OBJECTS O ON O.Object_Id = S.id
WHERE S.TEXT LIKE '%Table_name%'
    AND O.type = 'P'

It can search if a particular word is contained in the stored procedure. If a table name is what you need to find, then it can find it from the stored procedure text. Hope it helps.

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

How to pass list of table names to a stored procedure in SQL Server?

Ok, let's start off with the problems you have in your current set up. Firstly it sounds like you have a design flaw here. Most likely you are using a table's name to infer information that should be in a column. For example perhaps you have different tables for each client. In such a scenario the client's name should be a column in a singular table. This makes querying your data significantly easier and allows for good use for key constraints as well.

Next, your procedure. This is a huge security hole. The value of your dynamic object is not sanitised nor validated meaning that someone (malicious) has almost 100 characters to mess with your instance and inject SQL into it. There are many articles out there that explain how to inject securely (including by myself), and I'm going to cover a couple of processes here.

Note that, as per my original paragraph, you likely really have a design flaw, and so that is the real solution here. We can't address that in the answers here though, as we have no details of the data you are dealing with.

Fixing the injection

Injecting Securely

The basic's of injecting a dynamic object name is to make it secure. You do that by using QUOTENAME; it both delimit identifies the object name and escapes any needed characters. For example QUOTENAME(N'MyTable') would return an nvarchar with the value [MyTable] and QUOTENAME(N'My Alias"; SELECT * FROM sys.tables','"') would return the nvarchar value "My Alias""; SELECT U FROM sys.tables".

Validating the value

You can easily validate a value by checking that the object actually exists. I prefer to do this with the sys objects, so something like this would work:

SELECT @SchemaName = s.[name],
       @TableName = t.[name]
FROM sys.schemas s
     JOIN sys.tables t ON s.schema_id = t.schema_id
WHERE s.[name] = @Schema --This is a parameter
  AND t.[name] = @Table; --This is a parameter

As a result, if the FROM returns no values, then the 2 variables in the SELECT won't have a value assigned and no SQL will be run (as {String} + NULL = NULL).

The Solution

Table Type Parameter

So, to allow for multiple tables, we need a table type parameter. I would create one with both the schema and table name in the columns, but we can default the schema name.

CREATE TYPE dbo.Objects AS table (SchemaName sysname DEFAULT N'dbo',
                                  TableName sysname); --sysname is a sysnonym for nvarchar(128) NOT NULL

And you can DECLARE and INSERT into the TYPE as follows:

DECLARE @Objects dbo.Objects;

INSERT INTO @Objects (TableName)
VALUES(N'test');

Creating the dynamic statement

Assuming you are using a supported version of SQL Server, you'll have access to STRING_AGG; this removes any kind of looping from the procedure, which is great for performance. If you're using a version only in extended support, then use the "old" FOR XML PATH method.

This means you can take the values and create a dynamic statement along the lines of the below:

SET @SQL = (SELECT STRING_AGG(N'SELECT * FROM ' + QUOTENAME(s.[name]) + N'.' + QUOTENAME(t.[name]) + N';',' ')
            FROM sys.schemas s
                 JOIN sys.tables t ON s.schema_id = t.schema_id
                 JOIN @Objects O ON s.name = O.SchemaName
                                AND t.name = O.TableName);

The Stored Proecure

Putting all this together, this will give you a procedure that would look like this:

CREATE PROC schemaName.spDynamicTableName @Objects dbo.Objects AS
BEGIN

    DECLARE @SQL nvarchar(MAX),
            @CRLF nchar(2) = NCHAR(13) + NCHAR(10);

    SET @SQL = (SELECT STRING_AGG(N'SELECT N' + QUOTENAME(t.[name],'''') + N',* FROM ' + QUOTENAME(s.[name]) + N'.' + QUOTENAME(t.[name]) + N';',@CRLF) --I also inject the table's name as a column
                FROM sys.schemas s
                     JOIN sys.tables t ON s.schema_id = t.schema_id
                     JOIN @Objects O ON s.name = O.SchemaName
                                    AND t.name = O.TableName);

    EXEC sys.sp_executesql @SQL;

END;

And then you would execute it along the lines of:

DECLARE @Objects dbo.Objects;

INSERT INTO @Objects (SchemaName,TableName)
VALUES(N'dbo',N'MyTable'),
      (N'dbo',N'AnotherTable');

EXEC schemaName.spDynamicTableName @Objects;

A SQL query to list all stored procedures executed in a parent stored procedure

You can use the DMVs sys.procedures and sys.dm_sql_referencing_entities to get this info:

SELECT
    parent = referencing_schema_name + '.' + r.referencing_entity_name,
    child = QUOTENAME(OBJECT_SCHEMA_NAME(child.schema_id)) + '.' + QUOTENAME(child.name)
FROM sys.procedures AS child
JOIN sys.dm_sql_referencing_entities
    (QUOTENAME(OBJECT_SCHEMA_NAME(child.schema_id)) + '.' + QUOTENAME(child.name),
    'OBJECT') AS r;

List tables used in a stored procedure

Finally modified the code..

;WITH stored_procedures AS
(
SELECT 
o.name AS proc_name, oo.name AS table_name,
ROW_NUMBER() OVER(partition by o.name,oo.name ORDER BY o.name,oo.name)AS row
FROM sysdepends d 
INNER JOIN sysobjects o ON o.id=d.id
INNER JOIN sysobjects oo ON oo.id=d.depid
WHERE o.xtype = 'P'
)
SELECT proc_name, table_name FROM stored_procedures
WHERE row = 1 and proc_name in('sp1','sp2','sp3')
ORDER BY proc_name,table_name

Identifying all stored procedures and tables using a User-Defined Data Type in T-Sql

Refer to the article below on how to properly search for a string in a stored procedure definition:

Search text in stored procedure in SQL Server

As I posted in a comment above, ROUTINE_DEFINITION is an NVARCHAR(4000) and longer stored procedures have their definition truncated.

Your second method is not bad, it gets it done but yes your first is more correct.

List of Stored Procedure from Table

select
  so.name,
  sc.text
from
  sysobjects so inner join syscomments sc on so.id = sc.id
where
  sc.text like '%INSERT INTO xyz%'
  or sc.text like '%UPDATE xyz%'

This will give you a list of all stored procedure contents with INSERT or UPDATE in them for a particular table (you can obviously tweak the query to suit). Also longer procedures will be broken across multiple rows in the returned recordset so you may need to do a bit of manual sifting through the results.

Edit: Tweaked query to return SP name as well. Also, note the above query will return any UDFs as well as SPs.

---

Related Topics