August 21, 2011 at 2:41 am
Hi All,
Im just wondering is there a way in SQL where you can check a table and see what relations it has,
For example when you do an inner join you join two tables together on the same column.
Im curious to find out is there a way to find what table is related to what table without having to search through loads a tables....
Thanks.
August 21, 2011 at 8:26 am
What do you define as "being referenced"?
If you'ro looking for the refernces assigned by using foreign keys, you could look at sys.foreign_key_columns.
Other than that you'd need to analyze each object using sys.sql_modules if it contains the table name in question and see what table it is joined on.
But neither of those methods will tell you anything about ad-hoc queries. You might narrow it down even more by analyzing the cached plans using sys.dm_exec_cached_plans.
All three methods will allow you to find some of the references, but there's no way I know of to find all possible references "automagically".
August 22, 2011 at 4:04 am
Create a database diagram, it will just show the relation of all the tables involved.
Or else use the systables to find a particular relationship.
August 22, 2011 at 6:37 am
Are you thinking about more advanced code completion to speed writing sql statements in SQL Server Management Studio? If so I have had real good luck with Red Gate SQL Prompt Pro (http://www.red-gate.com/products/sql-development/sql-prompt/[/url]), when you type INNER JOIN it will come up with all the related tables that have foreign keys to the table your joining from.
I hope this helps if not the diagram suggestion was the next one I would make.
August 22, 2011 at 7:08 am
Run EXEC sp_depends 'Table_Name' to check the objects referred to the table
August 22, 2011 at 7:26 am
Remeber that looking up dependancies is only going to give you the results you want as long as relationships have been defined with primary \ foreign key relationships. I have worked with many DB's where these have been missing for both good and bad reasons.
MCITP SQL 2005, MCSA SQL 2012
August 23, 2011 at 12:27 pm
If DRI is used, this may give you an idea. This gives the References column, which is the lookup table and its column that ties to this column.
It may not be pretty, but it works.
SELECT
(select top 1 d.name from sys.data_spaces d where d.data_space_id =(select top 1 i.data_space_id from sys.indexes i where i.object_id = (SELECT top 1 t.object_id FROM sys.tables t WHERE gg.TABLE_NAME= t.name) and i.index_id < 2)) AS 'TableOnFileGroup',
TABLE_SCHEMA AS 'Schema Name',
TABLE_NAME AS 'Table Name',
COLUMN_NAME AS 'Data Element',
ORDINAL_POSITION AS 'OrdinalPosition',
DATA_TYPE AS 'Data Type',
ISNULL(CONVERT(char(10),CHARACTER_MAXIMUM_LENGTH),'') AS 'Max Len', ISNULL(CONVERT(char(10),NUMERIC_PRECISION),'') AS 'Prec', ISNULL(CONVERT(char(10),NUMERIC_SCALE),'') AS 'Scale',
CASE WHEN COLUMNPROPERTY(OBJECT_ID(TABLE_NAME), COLUMN_NAME, 'IsIdentity') = 1 then 'Y' ELSE '' END AS 'Identity',
CASE IS_NULLABLE WHEN 'YES' THEN '' ELSE 'NOT NULL' END AS 'NOT NULL',
ISNULL((SELECT top 1 object_name(k.referenced_object_id) + '(' + col_name(k.referenced_object_id,k.referenced_column_id) + ')' FROM sys.foreign_key_columns k WHERE object_name(k.parent_object_id) = gg.TABLE_NAME AND col_name(k.parent_object_id,k.parent_column_id) = gg.COLUMN_NAME),'') AS 'References',
ISNULL(COLUMN_DEFAULT,'') AS 'Default Value'
FROM information_schema.columns gg
ORDER BY [Schema Name], TABLE_CATALOG, TABLE_NAME, ORDINAL_POSITION
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply