February 24, 2020 at 4:05 pm
Hi
I use a DB that is used mainly for creating views and tables for another "data" db.
A table has changed and I need to find all the Stored Procs and views that use this table
found this code, but seem to only work withing the DB. Tried adding the full name 'OtherDB.dbo.ProgramSites'
select distinct [Table Name] = o.Name, [Found In] = sp.Name, sp.type_desc
from sys.objects o inner join sys.sql_expression_dependencies sd on o.object_id = sd.referenced_id
inner join sys.objects sp on sd.referencing_id = sp.object_id
and sp.type in ('P', 'FN')
where o.name = 'OtherDB.dbo.ProgramSites'
order by sp.Name
My other question is I figured to write a view that would get the info I need that was removed from the table and insert this view in all SP and Views to replace the original table. So say the orginal name was dbo.ProgramSites and my view would be say "'dbo.NewProgramSites" , is there any way to run some sort of update to replace the table name with the view name, or would each SP need to be changed manually?
Thanks
February 24, 2020 at 4:31 pm
Hi There,
I use the below code to find references to text (in this case a table name) in objects,
Replace TEXT_TO_FIND with the table name you are looking for,
It uses dynamic SQL and a Loop so is more than likely something that could be written better but it works for me and will hopefully help you out too,
USE MASTER
SET QUOTED_IDENTIFIER OFF
DECLARE @SearchText AS VARCHAR(100)
SELECT@SearchText = "TEXT_TO_FIND"
CREATE TABLE #TEMP (ID INT IDENTITY(1,1), Name VARCHAR(100))
INSERT INTO #TEMP (Name)
--Loads All Available Databases
SELECT"[" + name + "]" AS Name
FROMSysDatabases
WHEREDATABASEPROPERTYEX (Name, 'Status') = 'ONLINE'
CREATE TABLE #RESULTS (ServerName VARCHAR(100), DatabaseName VARCHAR(100), ObjectName VARCHAR(100),
ObjectType VARCHAR(100))
DECLARE @LoopID AS INT, @MaxLoopID AS INT, @DatabaseName AS VARCHAR(100),
@SQL AS VARCHAR(8000), @ServerName VARCHAR(100)
SELECT @LoopID = 1, @SQL = ""
SELECT @MaxLoopID = MAX(ID) FROM #TEMP
SELECT @ServerName = CAST((SELECT SERVERPROPERTY("MachineName")) AS VARCHAR(100))
--Searches Through All Databases For Search Text
WHILE @LoopID <= @MaxLoopID
BEGIN
SELECT @DatabaseName = Name FROM #TEMP WHERE #TEMP.ID = @LoopID
SELECT @SQL = @SQL + " INSERT INTO #RESULTS SELECT DISTINCT '" + @ServerName + "' AS ServerName,'" + @DatabaseName + "' AS DatabaseName, o.name, xtype FROM " + @DatabaseName + "..syscomments AS c JOIN" + @DatabaseName + "..sysobjects AS o ON c.[id] = o.[id] WHERE text LIKE ""%" + @SearchText + "%""" + CHAR(10) + ""
SELECT @LoopID = @LoopID + 1
END
EXEC(@SQL)
--SELECT @SQL
SELECT * FROM #RESULTS
DROP TABLE #TEMP
DROP TABLE #RESULTS
---------------------------------------------------------------------------------------
The more you know, the more you know that you dont know
February 24, 2020 at 4:43 pm
Hi Shyan,
Thank you very much, that worked great .
No All I need to find out is if'ts it's possible to update these 496 SP and Views in an automated fashion 🙂
February 24, 2020 at 4:48 pm
Hi
I use a DB that is used mainly for creating views and tables for another "data" db.
A table has changed and I need to find all the Stored Procs and views that use this table
Personally, I would use Redgate's "SQL Search" - a free plugin for SQL Server management studio - rather than rolling my own code to do that search... but then I'm lazy.
All I need to find out is if'ts it's possible to update these 496 SP and Views in an automated fashion 🙂
Well, you could write dynamic SQL to go through all the view definitions / SP definitions and alter them appropriately, but that's going to be deeply unpleasant, and possibly downright dangerous. I strongly recommend testing on a private copy of the database before going anywhere near the development system (let alone test or production etc)
Good luck!
Thomas Rushton
blog: https://thelonedba.wordpress.com
February 24, 2020 at 5:33 pm
Sorry mate not that im aware of. Object explorer (F7) on the views/procs so you can highlight multiple objects then right click drop and create to new window and do a find and replace on the table name. Not ideal and prone to error so definately try on a non production database but may save some time
---------------------------------------------------------------------------------------
The more you know, the more you know that you dont know
February 24, 2020 at 6:21 pm
I am not sure how a 'table' change requires updating/modifying all stored procedures/views/functions/etc... Do you mean to say that the old table is no longer used - and a new (different name) table is now being used?
If so - then how sure are you that all of the columns being referenced by the views/procedures/functions in your report database have not also changed? Is this new table a copy of the old table with all of the same columns?
I am assuming that the reference to the old table is something like: DataDB.dbo.OldTableName
To prevent this kind of issue in the future, you need to start utilizing SYNONYMS from the report database. This will allow for easy updating of the synonym to reference a new name (or even a new database with the same structure).
For example:
CREATE SYNONYM DataDB.TableName FOR DataDB.dbo.TableName;
Now in your code, instead of using 3-part naming you can use the synonym:
SELECT ... FROM DataDB.TableName
And if the name of the table changes - all you have to do is:
DROP SYNONYM DataDB.TableName;
CREATE SYNONYM DataDB.Table FOR DataDB.dbo.NewTableName;
And now all of your code is referencing the new table with no additional changes needed.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
February 24, 2020 at 6:35 pm
I believe OP was planning on replacing the name with a View but i agree a Synonym is a good way to go
---------------------------------------------------------------------------------------
The more you know, the more you know that you dont know
February 24, 2020 at 6:53 pm
Thanks for all the idea, first time I read about CREATE SYNONYM (I suck at this sometimes!!... lol)
I'm going to learn about this and start applying it ...
The problem is the one of the tables the vendor gave use had a derived field from two tables. They dropped that field on that table, so I figured to create a view in the report database, using the two tables, change all the SP's that use that table to point at the view.
February 24, 2020 at 8:46 pm
Note that the "Text" column of sys.comments is only NVARCHAR(4000), so if you have a longer stored procedure that view would not see the entire definition. For the complete definition, you should use sys.sql_modules "defintion" column, which is NVARCHAR(MAX).
February 24, 2020 at 9:16 pm
Thanks for all the idea, first time I read about CREATE SYNONYM (I suck at this sometimes!!... lol)
I'm going to learn about this and start applying it ...
The problem is the one of the tables the vendor gave use had a derived field from two tables. They dropped that field on that table, so I figured to create a view in the report database, using the two tables, change all the SP's that use that table to point at the view.
Okay - this makes sense, but you now need to search for both the table in question and the column. No need to modify the code if the column isn't referenced or used.
Hopefully, the name of that column is unique enough to filter down to just the code that is referencing that column.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
February 25, 2020 at 1:01 pm
First up, yes, a synonym is probably the easiest way to fix this.
Second, source control is your bestest buddy on earth. If you get all your code that defines databases and database objects into source control, when you do need to replace some definition across a wide swath of code, a simple search & replace will do the trick. Then, if you're also using source control as your deployment mechanisms (and you should be), you're all set for the next step.
Fundamentally, this is how all database development ought to be done (and only about 35-45% actually is).
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 25, 2020 at 4:17 pm
Grant/Jeff - won't any find/replace, in source control, via synonym or otherwise, have the same issues if the columns also changed? The only real method to make sure this works is to "automatically" assign lists out to developers, modify code and re-test, isn't it?
Or am I missing something and doing things the hard way? (likely)
-------------------------------------------------------------------------------------------------------------------------------------
Please follow Best Practices For Posting On Forums to receive quicker and higher quality responses
February 25, 2020 at 4:31 pm
Grant/Jeff - won't any find/replace, in source control, via synonym or otherwise, have the same issues if the columns also changed? The only real method to make sure this works is to "automatically" assign lists out to developers, modify code and re-test, isn't it?
Or am I missing something and doing things the hard way? (likely)
"If it's not in source control, it doesn't exist"
Start there and then manage all code through source control. Yes, if you modify columns, anything, there's nothing magical about how source control works. It just gives you a single point to easily modify a large number of objects. If I wanted to, for example, change the name of a column in 20 stored procedures, this is a trivial task in source control. Plus, I get the change tracking showing what changes i made, when etc.,. Then, if we're automating our dev, dev integration, continuous integration, qa, test, etc., we get to deploy and test that code on the fly.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply