Find all stored procedures that reference a specific column in some table
find all stored procedures that reference a table
get column names from stored procedure
sql query to find table used in stored procedures in mysql
find all references to a table in sql server
find stored procedure that inserts into a specific table
how to find table references in stored procedures
how to find all tables used in a stored procedure in sql server
I have a value in a table that was changed unexpectedly. The column in question is
CreatedDate: this is set when my item is created, but it's being changed by a stored procedure.
Could I write some type of
SELECT statement to get all the procedure names that reference this column from my table?
One option is to create a script file.
Right click on the database -> Tasks -> Generate Scripts
Then you can select all the stored procedures and generate the script with all the sps. So you can find the reference from there.
-- Search in All Objects SELECT OBJECT_NAME(OBJECT_ID), definition FROM sys.sql_modules WHERE definition LIKE '%' + 'CreatedDate' + '%' GO -- Search in Stored Procedure Only SELECT DISTINCT OBJECT_NAME(OBJECT_ID), object_definition(OBJECT_ID) FROM sys.Procedures WHERE object_definition(OBJECT_ID) LIKE '%' + 'CreatedDate' + '%' GO
SQL SERVER - Find Column Used in Stored , Can anyone tell me if there is a way to get the list of all the columns used in a stored procedure and also their table names? Reply. Nabeel Asif. Many years ago when I was still working on SQL 2000 I occasionally needed to find all stored procedures and views that referenced a certain table. After some research I found the system table syscomments. This table has a column “text” that contains the “Actual text of the SQL definition statement.”
If you want to get stored procedures using specific column only, you can use try this query:
SELECT DISTINCT Name FROM sys.Procedures WHERE object_definition(OBJECT_ID) LIKE '%CreatedDate%';
If you want to get stored procedures using specific column of table, you can use below query :
SELECT DISTINCT Name FROM sys.procedures WHERE OBJECT_DEFINITION(OBJECT_ID) LIKE '%tbl_name%' AND OBJECT_DEFINITION(OBJECT_ID) LIKE '%CreatedDate%';
https://social.msdn.microsoft.com/Forums/sqlserver, Scenario: A developer wants to drop a column from a table Find Stored Procedure Related to Table in Database – Search in All Stored Procedure Reference: Pinal Dave (https://blog.sqlauthority.com) only allow a certain role or privilege to see the data in that column even if its in a stored procedure? Using showplan_All as outlined in SQL Table and column Parser for stored procedures doesnt work for me, because this is a shared dev db. using a Sp from master db scanning system text as described is not feasible because I dont have access to the master db.
You can use ApexSQL Search, it's a free SSMS and Visual Studio add-in and it can list all objects that reference a specific table column. It can also find data stored in tables and views. You can easily filter the results to show a specific database object type that references the column
Disclaimer: I work for ApexSQL as a Support Engineer
How To Find the Objects and Columns a Stored Procedure or View , Find what objects are referenced in stored procedures or views. actually use the table or even the specific column in the table that you are changing. In my last post I covered finding objects that reference another object using the dmf, database that use several different objects to see how this works. SQL SERVER - Find Column Used in Stored Procedure - Search Stored Procedure for Column Name. Place: Any Developer Shop Scenario: A developer wants to drop a column from a table Time: Any Day - usually right before developer wants to go home The developer rushes to the manager and following conversation begins: Developer: I want to drop a column from one of the tables. Manager:….
You can use the system views contained in
information_schema to search in tables, views and (unencrypted) stored procedures with one script. I developed such a script some time ago because I needed to search for field names everywhere in the database.
The script below first lists the tables/views containing the column name you're searching for, and then the stored procedures source code where the column is found. It displays the result in one table distinguishing "BASE TABLE", "VIEW" and "PROCEDURE", and (optionally) the source code in a second table:
DECLARE @SearchFor nvarchar(max)='%CustomerID%' -- search for this string DECLARE @SearchSP bit = 1 -- 1=search in SPs as well DECLARE @DisplaySPSource bit = 1 -- 1=display SP source code -- tables if (@SearchSP=1) begin ( select '['+c.table_Schema+'].['+c.table_Name+'].['+c.column_name+']' [schema_object], t.table_type from information_schema.columns c left join information_schema.Tables t on c.table_name=t.table_name where column_name like @SearchFor union select '['+routine_Schema+'].['+routine_Name+']' [schema_object], 'PROCEDURE' as table_type from information_schema.routines where routine_definition like @SearchFor and routine_type='procedure' ) order by table_type, schema_object end else begin select '['+c.table_Schema+'].['+c.table_Name+'].['+c.column_name+']' [schema_object], t.table_type from information_schema.columns c left join information_schema.Tables t on c.table_name=t.table_name where column_name like @SearchFor order by c.table_Name, c.column_name end -- stored procedure (source listing) if (@SearchSP=1) begin if (@DisplaySPSource=1) begin select '['+routine_Schema+'].['+routine_Name+']' [schema.sp], routine_definition from information_schema.routines where routine_definition like @SearchFor and routine_type='procedure' order by routine_name end end
If you run the query, use the "result as text" option - then you can use "find" to locate the search text in the result set (useful for long source code).
Note that you can set
0 if you just want to display the SP names, and if you're just looking for tables/views, but not for SPs, you can set
Example result (find
CustomerID in the Northwind database, results displayed via LinqPad):
Note that I've verfied this script with a test view
and it found the
CustomerID in this view even though
c.* was used in the
SELECT statement (referenced table
Customers contains the
CustomerIDand hence the view is showing this column).
How do I find all stored procedures or views that use a specific table , needed to find all stored procedures and views that referenced a certain table. This table has a column “text” that contains the “Actual text of the SQL This DMV returns any object that references the object you pass it. You can use ApexSQL Search, it's a free SSMS and Visual Studio add-in and it can list all objects that reference a specific table column. It can also find data stored in tables and views. You can easily filter the results to show a specific database object type that references the column Disclaimer: I work for ApexSQL as a Support Engineer
SELECT Name FROM sys.procedures WHERE OBJECT_DEFINITION(OBJECT_ID) LIKE '%CreatedDate%' GO
or you can generate a scripts of all procedures and search from there.
How to find the columns and tables used in a stored procedure (SQL , Also if a procedure references something that is user schema work, otherwise there could be several procedures with the same name in different for the first table the procedure references, all columns in the second table Please remember, that text column in syscomments is varchar(255), so one big procedure can consist of many lines in syscomments, thus, the above selects will not find the procedure name if the table name, you are searching for, has been split into 2 text rows in syscomments.
Find objects used by specific stored procedure in SQL Server , Find objects used by specific stored procedure in SQL Server database. Bart Gawrych Table of Contents: Query; Columns; Rows; Sample Result Query below return all stored procedures and objects used by them. You can get it in a few minutes and enable your team make better use of your data. If you need to find database objects (e.g. tables, columns, triggers) by name - have a look at the FREE Red-Gate tool called SQL Search which does this - it searches your entire database for any kind of string(s).
Find stored procedures that reference a table column, We recently moved away from SQL Server replication and the database still has all of the rowguid columns and their associated indexes and Expand Stored Procedures, right-click the procedure and then click View Dependencies. View the list of objects that depend on the procedure. View the list of objects on which the procedure depends. Click OK. Using Transact-SQL. To view the dependencies of a procedure in Query Editor. System Function: sys.dm_sql_referencing_entities
SQL Server – Find Column Used in Stored Procedure – Search , How to find column used in a stored procedure. Find Stored Procedure Related to Table in Database – Search in All Stored Procedure · Search Reference: Pinal Dave (http://blog.sqlauthority.com) It looks like an article with general knowledge and no specific orientation to any programming language Earlier this week I wrote a blog about Find Column Used in Stored Procedure – Search Stored Procedure for Column Name. I received plenty of comments on the subject. One of the statements which I used in the story (Time: Any Day – usually right before developer wants to go home) was very much liked by many developers. I guess this is because we are all like the same. We often get more work
- Have a look at the
sys.all_sql_modulestable. In particular the column titled
- Possible duplicate : stackoverflow.com/questions/686247/…
- Does this just find objects / procedures with a defined parameter that matches the
WHEREclause, or would this also find references to
MyTable.SomeColumnwithin the objects themselves?
- Perfect! Works great for me. Thanks
- This doesn't give just the column named FirstName in a specific SP, but in any SP. This is fine until you have the same column name in use in several tables, views,.... There is no tool I know of that can find every place a specific column is referenced.