November 9, 2011 at 2:22 am
Hi,
I need to retrieve all the tables, stored procedure's, views, functions etc which are related to a particular field in a database.
For example if the field is EmployeeNumber, i need to list all the tables, sp's, views, functions etc. which are using EmployeeNumber. The main purpose is if I have made any change to the field(such as change the name of field) , i need to update all the places in the database where the field is being used.
Regards,
Nithin
November 9, 2011 at 2:29 am
Hakuna Matata (11/9/2011)
Hi,I need to retrieve all the tables, stored procedure's, views, functions etc which are related to a particular field in a database.
For example if the field is EmployeeNumber, i need to list all the tables, sp's, views, functions etc. which are using EmployeeNumber. The main purpose is if I have made any change to the field(such as change the name of field) , i need to update all the places in the database where the field is being used.
Regards,
Nithin
Could try querying object_definitions for some of it, but there's going to be some manual work sorting the wheat from the chaff.
SELECT specific_name
FROM information_schema.routines
WHERE object_definition(object_id(specific_name)) LIKE '%%'
So something like this?
DECLARE @sql VARCHAR(MAX), @column VARCHAR(50) = 'EmployeeNumber'
SELECT @sql = COALESCE(@SQL + '; ', '') + sql_code
FROM (SELECT 'USE ' + QUOTENAME(name) + '; ' + '
SELECT specific_name
FROM information_schema.routines
WHERE object_definition(object_id(specific_name)) LIKE ''%' + @column + '%''
UNION ALL
SELECT TABLE_NAME FROM information_schema.constraint_column_usage
WHERE COLUMN_NAME = ''' + @column + '''' AS sql_code
FROM sys.sysdatabases) a
EXEC(@SQL)
November 9, 2011 at 3:25 am
Cadavre (11/9/2011)
Hakuna Matata (11/9/2011)
Hi,I need to retrieve all the tables, stored procedure's, views, functions etc which are related to a particular field in a database.
For example if the field is EmployeeNumber, i need to list all the tables, sp's, views, functions etc. which are using EmployeeNumber. The main purpose is if I have made any change to the field(such as change the name of field) , i need to update all the places in the database where the field is being used.
Regards,
Nithin
Could try querying object_definitions for some of it, but there's going to be some manual work sorting the wheat from the chaff.
SELECT specific_nameFROM information_schema.routines
WHERE object_definition(object_id(specific_name)) LIKE '%%'
EDITSo something like this?
DECLARE @sql VARCHAR(MAX), @column VARCHAR(50) = 'EmployeeNumber'SELECT @sql = COALESCE(@SQL + '; ', '') + sql_code
FROM (SELECT 'USE ' + QUOTENAME(name) + '; ' + '
SELECT specific_name
FROM information_schema.routines
WHERE object_definition(object_id(specific_name)) LIKE ''%' + @column + '%''
UNION ALL
SELECT TABLE_NAME FROM information_schema.constraint_column_usage
WHERE COLUMN_NAME = ''' + @column + '''' AS sql_code
FROM sys.sysdatabases) a
EXEC(@SQL)
Thanks a looooooooot !!!!!! for taking time to help me out.
Your query will get me the SP's & functions. But I also need the tables that contain the field.
The below query also gets me the views referencing the field, but does not get me the Tables.
SELECT DISTINCT o.name AS ObjectName,
CASE o.xtype
WHEN 'C' THEN 'CHECK constraint'
WHEN 'D' THEN 'Default or DEFAULT constraint'
WHEN 'F' THEN 'FOREIGN KEY constraint'
WHEN 'FN' THEN 'Scalar function'
WHEN 'IF' THEN 'In-lined table-function'
WHEN 'K' THEN 'PRIMARY KEY or UNIQUE constraint'
WHEN 'L' THEN 'Log'
WHEN 'P' THEN 'Stored procedure'
WHEN 'R' THEN 'Rule'
WHEN 'RF' THEN 'Replication filter stored procedure'
WHEN 'S' THEN 'System table'
WHEN 'TF' THEN 'Table function'
WHEN 'TR' THEN 'Trigger'
WHEN 'U' THEN 'User table'
WHEN 'V' THEN 'View'
WHEN 'X' THEN 'Extended stored procedure'
ELSE o.xtype
END AS ObjectType,
ISNULL( p.Name, '[db]') AS Location
FROM syscomments c
INNER JOIN sysobjects o ON c.id=o.id
LEFT JOIN sysobjects p ON o.Parent_obj=p.id
WHERE c.text LIKE '%mysreference%'
ORDER BY Location, ObjectName
Any idea , how to get the tables referencing the filed????
November 9, 2011 at 5:53 am
i think something like this will help;
with the WHERE statement below, you get every objec that happens to have a reference to any column named "EmployeeNumber"
you might want to change it to name the table as well as the column to be sure you are referencing the specific table.
--note the view sys.sql_expression_dependencies is SQL 2008+ only
SELECT
depz.referenced_schema_name,
depz.referenced_entity_name,
objz.type_desc,
colz.name AS ColumnName
FROM sys.sql_expression_dependencies depz
INNER JOIN sys.objects objz ON depz.referenced_id=objz.object_id
LEFT OUTER JOIN sys.columns colz ON objz.object_id = colz.object_id
AND colz.column_id = referencing_minor_id
--WHERE referencing_id = OBJECT_ID(N'MyTable');
WHERE colz.name = 'EmployeeNumber'
Lowell
November 9, 2011 at 6:16 am
The main purpose is if I have made any change to the field(such as change the name of field) , i need to update all the places in the database where the field is being used.
The objective is good but I find some issues with the approach. If you search for a string say 'Employee' you may get N irrelevant Objects in 'Employee' schema or 'Employee' database (where everything is prefixed with ‘Employee’) and then you have to dig deep in the definitions to find the required ones.
As an alternative, you may select a tables (where you renamed the column) and search for dependencies from SSMS command. It can give you nested dependencies as well which is difficult with query approach.
November 9, 2011 at 6:23 am
Thanks a ton Lowell for the help. I am able to retrieve the Tables referencing the Field.:-):-):-):-)
November 9, 2011 at 6:36 am
Dev @ +91 973 913 6683 (11/9/2011)
The main purpose is if I have made any change to the field(such as change the name of field) , i need to update all the places in the database where the field is being used.
The objective is good but I find some issues with the approach. If you search for a string say 'Employee' you may get N irrelevant Objects in 'Employee' schema or 'Employee' database (where everything is prefixed with ‘Employee’) and then you have to dig deep in the definitions to find the required ones.
As an alternative, you may select a tables (where you renamed the column) and search for dependencies from SSMS command. It can give you nested dependencies as well which is difficult with query approach.
Point taken Dev!!!!!! I had not considered the issue pointed by you!!! Thank you !!!:-):-):-)
November 10, 2011 at 11:20 am
RedGate has a free SSMS plugin called "SQL Search" that will do exactly what you want. I just tried it on one my databases; given the name of a column, it returned results for tables, views, procedures, etc. When I click on an item in the search result list, the full object source is displayed with the search word highlighted in context.
http://www.red-gate.com/products/sql-development/sql-search/
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
November 10, 2011 at 12:11 pm
For searching SPs:
USE dbName
SELECT Distinct SO.Name
FROM sysobjects SO (NOLOCK)
INNER JOIN syscomments SC (NOLOCK) on SO.Id = SC.ID
AND SO.Type = 'P'
AND SC.Text LIKE '%Text needed%'
ORDER BY SO.Name
For searching columns:
SELECT o.name as [Table], c.name as [Column]
FROM SYSOBJECTS o JOIN SYSCOLUMNS c ON o.id = c.id
WHERE c.name like '%columnname%' AND o.xtype = 'u'
ORDER BY 1
Thanks,
Jared
Jared
CE - Microsoft
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply