Archive
BISQL#4:Identifying and Search of Relationship’s Are Always Difficult ,But Easy with Proper Analysis ..In SQL :)
Hi Friends,
- There is always question in my mind, what if I have two fields with me and how I am going to find relationship in-between two columns quickly only if I have very less idea on database
- This can be done without looking into actual database table just fire this query with proper input and column names at proper place
- Second one is used for Search a particular column in given Database
1.Following is query to find relation between any to column in given database
(1)—How to find Relation ShipBetween two coloumns
–drop table #TempAssociation
SELECT f.name AS ForeignKey,
SCHEMA_NAME(f.SCHEMA_ID) SchemaName,
OBJECT_NAME(f.parent_object_id) AS TableName,
COL_NAME(fc.parent_object_id,fc.parent_column_id) AS ColumnName,
SCHEMA_NAME(o.SCHEMA_ID) ReferenceSchemaName,
OBJECT_NAME (f.referenced_object_id) AS ReferenceTableName,
COL_NAME(fc.referenced_object_id,fc.referenced_column_id) AS ReferenceColumnName
into #TempAssociation
FROM sys.foreign_keys AS f
INNER JOIN sys.foreign_key_columns AS fc
INNER JOIN sys.objects AS o ON o.OBJECT_ID = fc.referenced_object_id
ON f.OBJECT_ID = fc.constraint_object_id
select * from #TempAssociation
where ColumnName like ‘%<First Column to Search>%’
or ColumnName like ‘%<Second Column to Search>%’
drop table #TempAssociation
Output is in the form of following table
Use above query where you don’t have to refer always towards database
2.How to search column in Database by its name
--Search Database Column by name SELECT table_name=sysobjects.name,
column_name=syscolumns.name, datatype=systypes.name, length=syscolumns.length FROM sysobjects JOIN syscolumns ON sysobjects.id = syscolumns.id JOIN systypes ON syscolumns.xtype=systypes.xtype WHERE syscolumns.name LIKE '%<Your Search Column Name>%'
Output is in the form of following table
For more interesting information on SQL we can also look into similar topics such as
· BISQL # 5 :Analyzing the Size of Database and Size of table
· BISQL # 6 :Identifying Running Query and Last Modified Query
· BISQL # 7 :ALL in One Database Hero Script !! Set–I
· BISQL # 8 : Pros and Cons of Money Data Type
· BISQL # 9 :How to Get Answers,Common Question :SQL Server FAQs eBook
Hope you will like this post on Search of Relationships.
If you really like reading my blog and understood at least few thing then please don’t forget to subscribe my blog.
If you want daily link and analysis or interesting link go to following website which will give @ your inbox please subscribe our following link resource blog: Link Resource Website
For More information related to BI World visit my Mentalist Blog
Link Resource Blog >> Daily Interesting links
SQL Server Mentalist >> SQL Learning Blog
Business Intelligence Mentalist >> BI World
Connect With me on
Copyright © 2011 – 2012 Vishal Pawar
SQL Server Mentalist 