sql script to show all references to a field
-
Does anyone have a script that searches all tables, stored procedures, etc. for all references to a field, or actually any text. I want to change a field and want to find all the places that it is used. I can probably write one myself, I just thought I would see if anyone else allready had one I could use.
-
I just ran Profiler while opening Show Dependancies on MyTable with 2 fields in the Design View in Enterprise Manager. It executed the statement for each of 2 fields:
exec sp_MSdependencies N'[dbo].[MyTable]', null, 1315327
exec sp_MSdependencies N'[dbo].[MyTable]', null, 1053183
by the name it is undocumented stored procedure, but there is a lot of on the web if you search for "SQL Server Undocumented Stored Procedures"
Or use Profiler to get correct syntax in your case.
Yelena
Regards,Yelena Varsha
-
you might want to use :
exec sp_depends @objname = 'object'
documented in BOL
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
The script below looks for references to any variable in all storedprocedures. You can save the file as .vbs and pass parameters next to it and execute it from command prompt. The name of the database is picked up by the variable sdatabase. Change your database name to one desired.
find_variable.vbs Servername user_name password variable
dim osqlserver
dim odatabase
dim sSQLserver
dim susername
dim spassword
dim var_find
ssqlserver = wscript.arguments(0)
susername = wscript.arguments(1)
spassword = wscript.arguments(2)
if wscript.arguments.count > 3 then
var_find = wscript.arguments(3)
else
var_find = ""
end if
sdatabase = "master"
set osqlserver = createobject("SQLDMO.SQLServer")
set odatabase = createobject("SQLDMO.database")
dim fs
set fs = createobject("Scripting.filesystemobject")
dim output
set output = fs.createtextfile("output.txt",true)
osqlserver.connect sSQLServer, susername, spassword
set odatabase = osqlserver.databases(sdatabase)
for each storedprocedure in odatabase.storedprocedures
if instr(1,storedprocedure.text,var_find,1) > 0 then
output.writeline (storedprocedure.text)
end if
next
This will give you name of all the tables with a specify column_name
SELECT OBJECT_NAME(ID) FROM SYSCOLUMNS WHERE NAME = column_name
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply