Applied on: Windows Hosting Accounts
When working with a large amount of objects in sql server there are times when you want to know what columns or fields are used in other objects (views, stored procedures, triggers, functions, etc). In SQL here are two queries that you can run to determine what if any dependencies there are.
Just replace the '%LastName%" with whatever you are looking for. The only thing you have to remember is if you have multiple table names with the same named column then you will get a result back with both tables. To prevent this then you might what to fully qualify the table names in your objects and then search for them that way. For example; if you a salesman table and a customer table with ‘LastName’ fields then this could be a problem.
The last query helps identify which tables are used in what Stored Procedure.
--Finds all tables that have a particular column SELECT SCHEMA_NAME(schema_id) AS schema_name, tb.name AS table_name, cl.name AS column_name FROM sys.tables AS tb INNER JOIN sys.columns cl ON tb.OBJECT_ID = c.OBJECT_ID WHERE cl.name LIKE '%LastName%' ORDER BY schema_name, table_name;
--Finds all objects which have the searched text in it. --This finds all objects that have '%LastName%' in the definition SELECT ao.type_desc , OBJECT_SCHEMA_NAME(sm.object_id) as schema_name, OBJECT_NAME(sm.object_id) as object_name FROM sys.sql_modules sm LEFT JOIN sys.all_objects ao on sm.object_id = ao.object_id WHERE [definition] LIKE '%LastName%'
This query will tell you what tables are used in stored Procedure
--Finds all tables that are used in stored Procedures Select * from (SELECT ii.name AS table_name,ou.name as proc_name, ROW_NUMBER() OVER(partition by ou.name,ii.name ORDER BY ou.name,ii.name) AS row FROM sysdepends d INNER JOIN sys.procedures ou on ou.[object_id]=d.id INNER JOIN sys.tables ii on ii.[object_id] = d.depid ) st where row = 1 order by proc_name, table_name
For quality SQL Server Hosting, best choice is groupaaa.com