| 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 tablesI 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. |
 |
|
|
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 |
|
|
|
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 |
|
|
|
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.... |
|
|
|
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. |
|
|
|
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 smWHERE 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.] |
|
|
|
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 ?.. |
|
|
|
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. |
|
|
|
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. |
|
|
|
|
List all the tables from Stored Procedure