Finding foreign key constraints in sql server

Sometimes when trying to truncate a table you need to remove all foreign keys referencing that table and that can be hard to do, since you cannot get a nice graphical overview.

To get the list of foreign key constraints for a given table you can run the following script exchanging the value in the @TABLENAME parameter.

DECLARE @TABLENAME VARCHAR(100);
SET @TABLENAME='';

SELECT
    K_Table = FK.TABLE_NAME,
    FK_Column = CU.COLUMN_NAME,
    PK_Table = PK.TABLE_NAME,
    PK_Column = PT.COLUMN_NAME,
    Constraint_Name = C.CONSTRAINT_NAME
FROM
    INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK
    ON C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK
    ON C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU
    ON C.CONSTRAINT_NAME = CU.CONSTRAINT_NAME
INNER JOIN (
            SELECT
                i1.TABLE_NAME,
                i2.COLUMN_NAME
            FROM
                INFORMATION_SCHEMA.TABLE_CONSTRAINTS i1
            INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE i2
                ON i1.CONSTRAINT_NAME = i2.CONSTRAINT_NAME
            WHERE
                i1.CONSTRAINT_TYPE = 'PRIMARY KEY'
           ) PT
    ON PT.TABLE_NAME = PK.TABLE_NAME
    
    WHERE PK.TABLE_NAME=@TABLENAME

 

Enjoy :)

How to check the progress of the Shrink Database task in SQL Server

When compacting large databases in SQL Server, whether or not its done via the DBCC SHRINKDATABASE command or done via the SQL Server Management Studio, you will experience a very long delay in the command returning.

To check how the process is going, you cannot simply look at a progress bar or se a percentage some nice place.

But you can use the system views in SQL Server to see how its going.

Simply open up a new query window in your SQL Server Management Studio and issue the following SQL:

SELECT 
	percent_complete, 
	start_time, 
	status, 
	command, 
	estimated_completion_time, 
	cpu_time, 
	total_elapsed_time
FROM 
	sys.dm_exec_requests
WHERE
	command = 'DbccFilesCompact'

That will give you a nice little table where you can see your compact task and how far it has gone in its progress.

But prepare to be disapointed - it is very slow to compact databases.