Monday, April 29, 2019

Foreign Key Reference Tables

To know about the database it is good for one if he/she looking for all references for a object

  • whats tables are dependent on MAIN table (Child Objects)
  • On which tables MAIN table is dependent (Parent Objects)
Suppose i have 3 tables and in parenthesis show its fields

Dept (dept_id, Dept_name )
Emp (emp_id, Emp_first_name, emp_last_name , dept_id)
Sal(sal_id, year, emp_id)

Here EMP is Main table and Dept is Parent table (for Main table) and Sal is Child table (for Main table).

So one can easily understand the database if he has knowledge of both kind of references

Below is the script to identify the same.


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
FROM 
sys.foreign_keys AS f
JOIN sys.foreign_key_columns AS fc ON f.OBJECT_ID = fc.constraint_object_id
JOIN sys.objects AS o ON o.OBJECT_ID = fc.referenced_object_id
where 
OBJECT_NAME(f.parent_object_id)='MAIN_TABLE'
or OBJECT_NAME (f.referenced_object_id) = 'MAIN_TABLE'

SQL Server Database Restore Audit

Do you want to know who restore the database, when restore the database, which database backup has been used.

Yes you can audit the database restore, see my previous post

How to Find Latest Database restored on Database and which backup file used

web stats