Query to find the foreign key relationships

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
INNER JOIN sys.foreign_key_columns AS fc ON f.OBJECT_ID = fc.constraint_object_id
INNER JOIN sys.objects AS o ON o.OBJECT_ID = fc.referenced_object_id

List of Tables with Row Count

Execute below script in a database to get Table Name and Rowcount .


SELECT
    t.NAME AS TableName,
    p.rows AS RowCounts
FROM
    sys.tables t
INNER JOIN
    sys.partitions p ON t.object_id = p.OBJECT_ID
WHERE
    t.NAME NOT LIKE 'dt%'
    AND t.is_ms_shipped = 0
 --   AND p.rows = 0
GROUP BY
    t.Name, p.Rows
ORDER BY
    t.Name

Database Struck in single user mode and cannot drop

Execute below script to find SPID which is holding the process

Use Master

GO

Select * from master.sys.sysprocesses

Where spid > 50

            And dbid=DB_ID (‘DBName’))  -- replace with your database name

Once you identified the spid to KILL, you can simply execute:

KILL 85 – replace the spid with the one returned

Then try to bring it back into multi-user role

ALTER DATABASE StuckDb SET MULTI_USER
GO