February 8, 2011 at 8:58 am
Does anyone know if it's possible to retrieve the column and table names being used by a stored procedure, and if so how? I only want the columns that have been USED, i don't want to return all the column names.
I should mention that the table and field names were trying to retrieve are through a linked database
Many thanks to anyone who can help me.
Neil. :crying:
February 8, 2011 at 9:29 am
The simplest way would be to look at the definition of the proc and go from there.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 8, 2011 at 9:36 am
That would be fine, however there are 50+ stored procedures accessing 100's of tables and wondered if there was an automated way of doing it. Otherwise I'm in the poo. 🙁
February 9, 2011 at 4:07 am
To add some more clarification
What I'm hoping to find is a list of dependencies for all of the stored procedures on our reports server, unfortunately our actual data source is on a different (linked) server and an approach like this :
SELECT DISTINCT o.name AS 'Procedure_Name', oo.name AS 'Table_Name'
FROM
sys.sysdepends AS D INNER JOIN
sys.sysobjects AS o ON D.id = o.id INNER JOIN
sys.sysobjects AS oo ON D.depid = oo.id
ORDER BY 'Procedure_Name', 'Table_Name'
Only lists dependencies to tables on the same server.
Is there an alternative that would include the tables on the linked server too?
February 9, 2011 at 4:39 am
neil.bower (2/9/2011)
Is there an alternative that would include the tables on the linked server too?
Not built in.
Maybe try something like RedGate's SQL Dependency Tracker
http://www.red-gate.com/products/sql-development/sql-dependency-tracker/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 10, 2011 at 1:34 am
We managed to put together something close to a solution using this.
The below takes the full list of tables on the linked server and checks if they are used anywhere in each stored procedure text on the report server (not a fast process but it got the results out after about 40 minutes). [D-DB10].ss_fwmis is the linked server our data is held on.
SELECT DISTINCT
OBJECT_SCHEMA_NAME (Sys.id) AS Schema_Name,
OBJECT_NAME (Sys.id) AS Object_Name,
fwi.name AS FWI_Table
FROM
[D-DB10].ss_fwmis.dbo.sysobjects AS fwi
CROSS JOIN sys.syscomments AS Sys
WHERE
Sys.text LIKE '%ss_fwmis.dbo.' + fwi.name + '%'
ORDER BY
OBJECT_SCHEMA_NAME (Sys.id),
OBJECT_NAME (Sys.id)
Then the table list from above was used to get a list of every field name that might appear in the stored procedures and a similar query below used to check if those field names appear:
;WITH Field_List AS
(
SELECT
fwi.Used_Tables.Object_Name, --The Stored Procedure the field may exist in
fwi.Used_Tables.FWI_Table,
fwi.All_Fields_in_Used_Tables.COLUMN_NAME
FROM
fwi.Used_Tables
INNER JOIN
fwi.All_Fields_in_Used_Tables
ON fwi.Used_Tables.FWI_Table = fwi.All_Fields_in_Used_Tables.TABLE_NAME
)
SELECT DISTINCT
OBJECT_SCHEMA_NAME (Sys.id) AS Schema_Name,
OBJECT_NAME (Sys.id) AS Object_Name,
FWI_Table,
Field_List.COLUMN_NAME
INTO fwi.Used_Fields
FROM
sys.syscomments AS Sys INNER JOIN Field_List ON OBJECT_NAME (Sys.id) = Field_List.Object_Name
WHERE
Sys.text LIKE '%' + COLUMN_NAME + '%'
ORDER BY
OBJECT_SCHEMA_NAME (Sys.id),
OBJECT_NAME (Sys.id)
It's not 100% accurate because when one field name appears in more than one table the query cannot tell which has been used but we now have a significantly shorter list of fields and tables to run through than we did before.
We also gave RedGate Dependency Tracker a go but unfortunately it only goes down to table level for linked databases.
Barney
February 10, 2011 at 2:37 am
Sweeeeet! Works like a charm.
Thanks Barney!;-)
Neil.
February 10, 2011 at 5:27 am
Just note that sysobject and syscomments are deprecated, should not be used in new development and will be removed from a future version of the product. Replacements are sys.comments and sys.sql_modules
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 19, 2016 at 12:37 pm
It took me a minute to work out a query that would tell me the specific tables AND FIELDS that are used in a stored procedure. I am trying to build requirements for our data warehouse and don't necessarily need to bring over every field in every table. This works great for me (I'm using 2012), with one exception noted below.
SELECT DISTINCT
O.name SP_Name,T.name Table_Name,c.name Field_Name
FROM sys.sysdepends D
JOIN sys.sysobjects O ON O.id = D.id
JOIN sys.sysobjects T ON T.id = D.depid
JOIN sys.columns C ON C.column_id=d.depnumber
and C.object_id=D.depID
WHERE O.xtype = 'P'
and o.name = 'sp_yourstoredprocedure'
The only time this hasn't worked for me is when the stored procedure was built into a #temp table first with results returning from the #temp table. Those are poor code and need to be re-written anyway. :/
December 6, 2017 at 12:07 pm
/*
Task :- To get all table and column name which is used in stored procedure
Please see the below query and that will help you to understand logic .
*/
IF OBJECT_ID('tempdb..#tbl') IS NOT NULL
DROP TABLE #tbl
IF OBJECT_ID('tempdb..#tblError') IS NOT NULL
DROP TABLE #tblError
DECLARE @MinSrNo INT , @MaxSrNo INT
DECLARE @SpName NVARCHAR(100)
DECLARE @Statement NVARCHAR(1000)
DECLARE @tblSp TABLE
(
SrNo INT IDENTITY(1, 1) ,
StoredProcedureName NVARCHAR(100)
)
CREATE TABLE #tbl
(
SrNo INT IDENTITY(1, 1) ,
DatabaseName NVARCHAR(50) ,
StoredProcedureName NVARCHAR(100) ,
ObjectType NVARCHAR(50) ,
ObjectName NVARCHAR(100) ,
ColumnName NVARCHAR(100) ,
OrdinalPosition INT
)
CREATE TABLE #tblError
(
SrNo INT IDENTITY(1, 1) ,
StoredProcedureName NVARCHAR(100) ,
ErrorNumber INT ,
ErrorLog NVARCHAR(MAX)
)
INSERT INTO @tblSp ( StoredProcedureName )
SELECT name FROM sys.procedures ORDER BY name
SELECT @MinSrNo = MIN(SrNo) , @MaxSrNo = MAX(SrNo) FROM @tblSp
WHILE ( @MinSrNo <= @MaxSrNo )
BEGIN
SELECT @SpName = '' , @Statement = ''
SELECT @SpName = StoredProcedureName FROM @tblSp WHERE SrNo = @MinSrNo
SET @Statement = 'SELECT DatabaseName = ISNULL(referenced_database_name, DB_NAME()) , StoredProcedureName = ' + CHAR(39) + @SpName + CHAR(39)
+ ' , SO.ObjectType , ObjectName = referenced_entity_name , ColumnName = referenced_minor_name , OrdinalPosition = referenced_minor_id FROM sys.dm_sql_referenced_entities('
+ CHAR(39) + '[dbo].[' + @SpName + ']' + CHAR(39) + ', ' + CHAR(39) + 'OBJECT' + CHAR(39)
+ ') R CROSS APPLY ( SELECT ObjectType = ( CASE type_desc WHEN '
+ CHAR(39) + 'VIEW' + CHAR(39) + ' THEN ' + CHAR(39) + 'View' + CHAR(39) + ' WHEN ' + CHAR(39) + 'USER_TABLE' + CHAR(39)
+ 'THEN' + CHAR(39) + 'Table' + CHAR(39)
+ 'END ) FROM sys.objects WHERE object_id = R.referenced_id ) SO '
+ ' WHERE referenced_minor_name IS NOT NULL'
PRINT (@Statement)
BEGIN TRY
INSERT INTO #tbl ( DatabaseName , StoredProcedureName , ObjectType , ObjectName , ColumnName , OrdinalPosition )
EXEC (@Statement)
END TRY
BEGIN CATCH
INSERT INTO #tblError ( StoredProcedureName , ErrorNumber , ErrorLog )
SELECT @SpName , ERROR_NUMBER() , ERROR_MESSAGE()
END CATCH
SET @MinSrNo += 1
END
SELECT *
FROM #tbl
ORDER BY StoredProcedureName , ObjectType , ObjectName , OrdinalPosition
SELECT * FROM #tblError
IF OBJECT_ID('tempdb..#tbl') IS NOT NULL
DROP TABLE #tbl
IF OBJECT_ID('tempdb..#tblError') IS NOT NULL
DROP TABLE #tblError
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply