Friday, 28 March 2014

Find out the Foreign Key column name ,table name & references table, column names?

SELECT 
    F.NAME AS FOREIGNKEY,
    OBJECT_NAME(F.PARENT_OBJECT_ID) AS TABLENAME,
    COL_NAME(FC.PARENT_OBJECT_ID,
    FC.PARENT_COLUMN_ID) AS COLUMNNAME,
    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
    INNER JOIN SYS.FOREIGN_KEY_COLUMNS AS FC ON F.OBJECT_ID = FC.CONSTRAINT_OBJECT_ID
    ORDER BY    COL_NAME(FC.REFERENCED_OBJECT_ID,FC.REFERENCED_COLUMN_ID)

Table size and rows of tables in sql server

Below query  will help us, to know tables size and now  of rows in a table.  SELECT     SCHEMA_NAME(t.schema_id) AS SchemaName,     t.name A...