Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 List all the tables from Stored Procedure

Author  Topic 

sql_chaser
Starting Member

33 Posts

Posted - 2014-11-26 : 14:34:57
Hi Team,

I'm not able to list all the linked server and openquery tables from the stored procedures.

I tried sysdepends - Gave me all the tables
I tried sp_helptext in a loop - Gave me all the Linked server calls but not openquery calls as the reference to the tables are in a different row.

Please let me know if anyone has a query to all the references !!!

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-11-27 : 10:27:54
I think that you'll need to scan the text of the procs to find OPENQUERY calls.
Go to Top of Page

sql_chaser
Starting Member

33 Posts

Posted - 2014-12-01 : 13:18:47
I tried using the below query but the "linked server" calls that comes under "OpenQuery" is not coming out as it's on a different row...

-- create temporary Result table to gather names and textof the procedures in the DataBaseName database :
CREATE TABLE #Result
(TextField varchar(max), ProcName varchar(100))

-- create temporary ProcName table with the names of all the procedures in the database [DataBaseName]:
CREATE TABLE #ProcList
(ID int IDENTITY, ProcName varchar(100))

--populate the ProcName table with the procedure names:
INSERT #ProcList SELECT b.name + '.'+ a.name as [name] from sys.procedures a inner join sys.schemas b on a.schema_id=b.schema_id

--get the number of procedures (to be used in the loop below):
DECLARE @NumberOfProcs int
SELECT @NumberOfProcs = COUNT(*) FROM sys.procedures

--loop to populate the Result table:
DECLARE @i INT
SET @i = 1
DECLARE @ProcName varchar(100)
DECLARE @SQL varchar(2000)
WHILE @i <= @NumberOfProcs
BEGIN
SELECT @ProcName = ProcName FROM #ProcList WHERE ID = @i
SET @SQL = 'INSERT INTO #Result (TextField) EXEC sp_helptext ' + ''''+ @ProcName +''''
EXEC (@SQL)
UPDATE #Result SET ProcName = @ProcName WHERE ProcName IS NULL
SET @i = @i + 1
END

--look for a string you need [your string] in the Result table
SELECT distinct LTRIM(rtrim(TextField)) as TextField,ProcName FROM #Result WHERE TextField LIKE '%LinkedServerName%' order by ProcName

--clean up
DROP TABLE #Result
DROP TABLE #ProcList
Go to Top of Page

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-12-01 : 13:54:05
Script the procs to text files, use something like Notepad++ to scan for OpenQuery
Go to Top of Page

sql_chaser
Starting Member

33 Posts

Posted - 2014-12-01 : 18:30:23
I tried the Notepad++ but the Open Query has subquery and doesn't follow the same pattern so hard to define a search pattern. One option is to get all the text after OpenQuery till ') value as all but need to search for each character.

If there any other methods...This will help a lot of analyst and developers out there for impact analysis....
Go to Top of Page

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-12-02 : 09:59:38
It sounds like you might need to write a custom program to do the analysis. F# is particularly adept at pattern matching, though you can do it in C# easily enough.
Go to Top of Page

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2014-12-02 : 11:44:18
The quick way to search for given text in SQL code is this:

SELECT sm.*
FROM sys.sql_modules sm
WHERE
definition LIKE '%OPENQUERY%'

[In the rare event that your db is case sensitive, you'll need to add a COLLATE clause above to cancel that out.]
Go to Top of Page

sql_chaser
Starting Member

33 Posts

Posted - 2014-12-02 : 14:04:00
Would the above query return if I'm not using the openquery and just calling by LinkedServerName.DBName.SchemaName.TableName ?..
Go to Top of Page

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-12-02 : 14:15:19
no but you could extend the WHERE Clause to catch it.
Go to Top of Page

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2014-12-03 : 10:33:04
No, but if the reference is in static SQL, you can use view:

sys.sql_expression_dependencies

to find it and other such references. sysdepends is an obsolete view and should not be used any longer.
Go to Top of Page
   

- Advertisement -