¿Cómo puedo obtener la lista de tablas en el procedimiento almacenado?

Hay muchas tablas y sp en el db. Encuentro el nombre de las tablas que se usan en el sp específico (procedimiento almacenado).

sp_depends %sp_name% no da el resultado %sp_name% . También me utilizan las tablas INFORMATION_SCHEMA.TABLES , INFORMATION_SCHEMA.ROUTINES .

pero el resultado no está lleno llene mi requerimiento.

 ;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 ORDER BY proc_name,table_name 

Pruebe de una forma más elegante (pero su solución funciona solo en MS SQL 2008 o superior) –

 SELECT DISTINCT [object_name] = SCHEMA_NAME(o.[schema_id]) + '.' + o.name , o.type_desc FROM sys.dm_sql_referenced_entities ('dbo.usp_test1', 'OBJECT') d JOIN sys.objects o ON d.referenced_id = o.[object_id] WHERE o.[type] IN ('U', 'V') 

Aquí está el código sql para esto

Para obtener la lista de tablas utilizadas en un procedimiento almacenado

 ;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 ORDER BY proc_name,table_name 

.

Invertir – Para encontrar el procedimiento almacenado relacionado con la tabla en la base de datos – Buscar en todos los procedimientos almacenados

Hay dos formas de esto

 ----Option 1 SELECT DISTINCT so.name FROM syscomments sc INNER JOIN sysobjects so ON sc.id=so.id WHERE sc.TEXT LIKE '%tablename%' ----Option 2 SELECT DISTINCT o.name, o.xtype FROM syscomments c INNER JOIN sysobjects o ON c.id=o.id WHERE c.TEXT LIKE '%tablename%' 

PD: sp_help y sp_depends no siempre arrojan resultados precisos.

Referencia:

  1. Sql Server Central – Obtener lista de tablas usadas en un procedimiento almacenado
  2. Autoridad SQL – Buscar procedimiento almacenado relacionado con la tabla en la base de datos – Buscar en todos los procedimientos almacenados

Las dos respuestas más votadas utilizan una gran cantidad de tablas obsoletas que deben evitarse.
Aquí hay una manera mucho más limpia de hacerlo.

Obtenga todas las tablas de las que depende un procedimiento almacenado:

 SELECT DISTINCT p.name AS proc_name, t.name AS table_name FROM sys.sql_dependencies d INNER JOIN sys.procedures p ON p.object_id = d.object_id INNER JOIN sys.tables t ON t.object_id = d.referenced_major_id ORDER BY proc_name, table_name 

Funciona con MS SQL SERVER 2005+

Lista de cambios:

  • sysdepends deben reemplazar por sys.sql_dependencies
    • La nueva tabla usa object_id lugar de id
    • La nueva tabla utiliza referenced_major_id lugar de depid
  • El uso de sysobjects debería reemplazarse por vistas de catálogo de sistema más enfocadas
    • Como señaló marc_s , en su lugar use sys.tables y sys.procedures
    • Nota : Esto evita tener que verificar dónde o.xtype = 'p' (etc.)
  • Además, realmente no hay necesidad de un CTE que use ROW_NUMBER() solo para asegurarnos de que solo tenemos uno de cada conjunto de registros devuelto. ¡Para eso está DISTINCT !

    • De hecho, SQL es lo suficientemente inteligente como para usar DISTINCT detrás de escena.
    • Presento evidencia: Prueba A. ¡Las siguientes consultas tienen el mismo plan de ejecución !

       -- Complex WITH MyPeople AS ( SELECT id, name, ROW_NUMBER() OVER(PARTITION BY id, name ORDER BY id, name) AS row FROM People) SELECT id, name FROM MyPeople WHERE row = 1 -- Better SELECT DISTINCT id, name FROM People 
 SELECT NAME FROM SYSOBJECTS WHERE ID IN ( SELECT SD.DEPID FROM SYSOBJECTS SO, SYSDEPENDS SD WHERE SO.NAME = 'SP_NAME' AND SD.ID = SO.ID ) 

Aquí hay un ejemplo para encontrar la lista de tablas usadas en un procedimiento

 ;WITH procs AS ( SELECT o1.name AS proc_name, o2.name AS table_name, ROW_NUMBER() OVER(PARTITION BY o1.name,o2.name ORDER BY o1.name,o2.name) AS row FROM sysdepends d INNER JOIN sysobjects o1 ON o1.id=d.id INNER JOIN sysobjects o2 ON o2.id=d.depid WHERE o1.xtype = 'P' --AND o2.name = 'tabname1' OR o2.name = 'tblname2' ) SELECT proc_name, table_name FROM procs WHERE row = 1 ORDER BY proc_name, table_name 

Además, esta consulta devuelve todos los nombres de tabla de todas las tablas dependientes en un procedimiento Almacenado.

 SELECT DISTINCT o.id, o.name as 'Procedure_Name' , oo.name as 'Table_Name' FROM sysdepends d, sysobjects o, sysobjects oo WHERE o.id=d.id and oo.id=d.depid and depnumber=1 ORDER BY o.name,oo.name 

Parece que no hay una respuesta completa para la pregunta OP. La mayoría de las respuestas anteriores no tienen ningún nombre de esquema ni incluyen otros objetos (por ejemplo, funciones) utilizados en los procedimientos almacenados.

Lista completa de tablas / vistas usadas en procedimientos almacenados con nombre de esquema e id de objeto

 SELECT DISTINCT procObj.[object_id] AS [ProcObjectId], procSchema.[name] AS [ProcSchema], procObj.[Name] AS [ProcName], tableObj.[object_id] AS [TableObjectId], tableSchema.[name] AS [TableSchema], tableObj.[Name] AS [TableName] FROM sys.sql_dependencies AS dep INNER JOIN sys.objects AS procObj ON procObj.[object_id] = dep.[object_id] INNER JOIN sys.schemas AS procSchema ON procSchema.[schema_id] = procObj.[schema_id] INNER JOIN sys.objects AS tableObj ON tableObj.[object_id] = dep.[referenced_major_id] INNER JOIN sys.schemas AS tableSchema ON tableSchema.[schema_id] = tableObj.[schema_id] WHERE procObj.[type] = 'P' -- using this filter we can control dependent object types -- eg tableObj.[type] IN ('U') - returns tables only AND tableObj.[type] IN ('V', 'U') 

Tenga en cuenta que hay un filtro en los tipos de objetos dependientes que pueden modificarse (depende de lo que desee en los resultados de salida). La lista completa de abreviaturas de tipo está aquí .

La respuesta de KyleMit es usar una tabla que también se depreciará pronto. Se recomienda utilizar sys.sql_experssion_dependencies en lugar de sys.sql_dependencies, por ejemplo,

 SELECT DISTINCT p.name AS proc_name, t.name AS table_name FROM sys.sql_expression_dependencies d INNER JOIN sys.procedures p ON p.object_id = d.referencing_id INNER JOIN sys.tables t ON t.object_id = d.referenced_id ORDER BY proc_name, table_name 

Esto debería funcionar con SQL Server 2008+.

No tenía una reputación lo suficientemente alta como para comentar directamente sobre la respuesta a la que se hace referencia.

Pero tenga en cuenta que los sysdepends no darán los nombres de la tabla si se usan en sql dynamic . Lo que sugiero es buscar de forma inversa, es decir, crear una búsqueda en bucle en las tablas de los syscomments . El procedimiento almacenado a continuación puede ayudar

 CREATE PROCEDURE dbo.sp_getObjects ( @ObjName VARCHAR(255) ) AS BEGIN SET NOCOUNT ON DECLARE @Idkeyst INTEGER DECLARE @Idkeyed INTEGER DECLARE @tblName VARCHAR(255) DECLARE @Objects VARCHAR(MAX) IF NOT EXISTS(SELECT 1 FROM sys.objects where NAME = @ObjName AND type in ('P', 'FN','TR')) BEGIN PRINT 'NO Text Available for the Parameter' RETURN(0) END CREATE TABLE #ProcStr ( Idkey INT IDENTITY(1,1), ScriptStr VARCHAR(MAX) ) CREATE TABLE #Depends ( Idkey INT IDENTITY(1,1), Depends VARCHAR(255) ) CREATE TABLE #Objects ( Idkey INT IDENTITY(1,1), ObjectName VARCHAR(255) ) INSERT INTO #ProcStr (ScriptStr) EXEC sp_helptext @ObjName DELETE #ProcStr WHERE LTRIM(ScriptStr) LIKE '--%' DELETE #ProcStr WHERE LTRIM(REPLACE(ScriptStr,CHAR(9),'')) LIKE '--%' SET @Idkeyst = 0 SET @Idkeyed = 0 WHILE 1=1 BEGIN SELECT @Idkeyst = MIN(idKey) FROM #ProcStr WHERE ScriptStr like '%/*%' and Idkey > @Idkeyst IF @Idkeyst IS NULL BREAK SELECT @Idkeyed = MIN(idKey) FROM #ProcStr WHERE ScriptStr like '%*/%' and Idkey >= @Idkeyst DELETE #ProcStr WHERE Idkey >= @Idkeyst and Idkey <=@Idkeyed END DELETE #ProcStr WHERE ISNULL(LTRIM(REPLACE(ScriptStr,CHAR(9),'')),'')='' INSERT INTO #Depends (Depends) SELECT DISTINCT t.name FROM sys.sql_dependencies d INNER JOIN sys.procedures p ON p.object_id = d.object_id INNER JOIN sys.tables t ON t.object_id = d.referenced_major_id where p.name = @ObjName INSERT INTO #Objects SELECT name from sys.objects o WHERE Type = 'U' AND NOT EXISTS (SELECT 1 FROM #Depends WHERE Depends = o.name) SET @Objects = '' SELECT @Objects = CASE WHEN ISNULL(@Objects,'') = '' THEN '' ELSE @Objects+', ' END+ Depends FROM #Depends UPDATE #ProcStr SET ScriptStr = LTRIM(RTRIM(ScriptStr)) UPDATE #ProcStr SET ScriptStr = REPLACE(ScriptStr,CHAR(9),'') UPDATE #ProcStr SET ScriptStr = REPLACE(ScriptStr,CHAR(13),'') UPDATE #ProcStr SET ScriptStr = REPLACE(ScriptStr,CHAR(10),'') SET @tblName = '' SET @Idkeyst = 0 WHILE 1=1 BEGIN SELECT @Idkeyst = MIN(idKey) FROM #Objects WHERE Idkey > @Idkeyst IF @Idkeyst IS NULL BREAK SELECT @tblName = ObjectName FROM #Objects WHERE Idkey = @Idkeyst IF Exists (SELECT 1 FROM #ProcStr WHERE (ScriptStr LIKE '% '+@tblName+' %' OR ScriptStr LIKE '%.'+@tblName+' %' OR ScriptStr LIKE @tblName+' %' OR ScriptStr LIKE @tblName --OR ScriptStr LIKE '%'+@tblName OR ScriptStr LIKE '% '+@tblName+'''%' OR ScriptStr LIKE @tblName+'''%')) BEGIN SET @Objects = CASE WHEN ISNULL(@Objects,'')<>'' THEN @Objects+', '+@tblName ELSE @tblName END END END IF ISNULL(@Objects,'') = '' BEGIN PRINT 'NO Tables are reffered in the stored procedures' RETURN(0) END PRINT @Objects SET NOCOUNT OFF END 

Este código examina de forma recurrente cada Procedimiento almacenado en su Procedimiento almacenado y le proporciona la lista completa de todas las tablas utilizadas.

 declare @sp_name varchar(100) declare @curSpName varchar(100) declare @curObjName varchar(255) declare @curXType varchar(1)​ create table #tmpTables ( proc_name varchar(255), table_name varchar(255) )​ set @sp_name = 'STORED_PROCEDURE_NAME'​ ;WITH stored_procedures AS ( SELECT o.name AS proc_name, oo.name AS table_name, oo.type AS xType, ROW_NUMBER() OVER(partition by o.name,oo.name, oo.Type ORDER BY o.name,oo.name, oo.Type) 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' --and oo.type = 'U' --Tables and o.name = @sp_name) SELECT proc_name, table_name, xType, 'N' Processed into ##tmpSP FROM stored_procedures WHERE row = 1 --ORDER BY proc_name,table_name​​ While (Select count(*) from ##tmpSP where Processed = 'N') 0 Begin​ Select top 1 @curSpName = proc_name, @curObjName = table_name, @curXType = xType from ##tmpSP where Processed = 'N'​​ if @curXType = 'U' Begin​ insert into #tmpTables values (@curSpName, @curObjName)​ End​ if @curXType = 'P' Begin​ ;WITH stored_procedures AS ( SELECT o.name AS proc_name, oo.name AS table_name, oo.type AS xType, ROW_NUMBER() OVER(partition by o.name,oo.name, oo.Type ORDER BY o.name,oo.name, oo.Type) 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' and oo.type = 'U' --Tables and o.name = @curObjName) insert into #tmpTables SELECT @curSpName, table_name FROM stored_procedures WHERE row = 1 ORDER BY proc_name,table_name​ ;WITH stored_procedures AS ( SELECT o.name AS proc_name, oo.name AS table_name, oo.type AS xType, ROW_NUMBER() OVER(partition by o.name,oo.name, oo.Type ORDER BY o.name,oo.name, oo.Type) 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' and oo.type = 'P' --SP's and o.name = @curObjName) insert into ##tmpSP SELECT proc_name, table_name, xType, 'N' FROM stored_procedures WHERE row = 1 and proc_name not in ( Select proc_name from ##tmpSP ) ORDER BY proc_name,table_name​ End​ if @curXType = 'v' Begin​ ;WITH stored_procedures AS ( SELECT o.name AS proc_name, oo.name AS table_name, oo.type AS xType, ROW_NUMBER() OVER(partition by o.name,oo.name, oo.Type ORDER BY o.name,oo.name, oo.Type) 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 = 'v' and oo.type = 'U' --Tables and o.name = @curObjName) insert into #tmpTables SELECT proc_name, table_name FROM stored_procedures WHERE row = 1 ORDER BY proc_name,table_name​ End​ update ##tmpSP set Processed = 'Y' where table_name = @curObjName​​ End​​ Select distinct table_name from #tmpTables​ drop table #tmpTables drop table ##tmpSP