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

Script to see running jobs in SQL Server

Script to check the Sql Agent Running Jobs

SELECT

    *

FROM msdb.dbo.sysjobactivity ja

LEFT JOIN msdb.dbo.sysjobhistory jh

    ON ja.job_history_id = jh.instance_id

JOIN msdb.dbo.sysjobs j

ON ja.job_id = j.job_id

JOIN msdb.dbo.sysjobsteps js

    ON ja.job_id = js.job_id

    AND ISNULL(ja.last_executed_step_id,0)+1 = js.step_id

WHERE ja.session_id = (SELECT TOP 1 session_id FROM msdb.dbo.syssessions ORDER BY agent_start_date DESC)

AND start_execution_date is not null

AND stop_execution_date is null;

LEAD in TSQL

LEAD has introduced in SQL Server 2012 to access data from a subsequent row in the same resultset without the use of self-join . LAG is a analytical function in a select statement to compare values in the current row with values in a previous row.



Create Table Sales( empid int, Year int, sales money)

insert into Sales values(1,2000,100)

insert into Sales values(1,2001,200)

insert into Sales values(1,2002,300)

insert into Sales values(2,2001,200)

insert into Sales values(2,2002,300)

--Subsequent Row   SELECT empid, Year, sales,

lead(sales, 1, 0) OVER (PARTITION BY empid ORDER BY Year) AS PrevProfit FROM Sales

Results :

empid Year sales PrevProfit

----------- ----------- --------------------- ---------------------

1 2000 100.00 200.00

1 2001 200.00 300.00

1 2002 300.00 0.00

2 2001 200.00 300.00

2 2002 300.00 0.00
 

LAG in TSQL

LAG has introduced in SQL Server 2012 to access data from a Previous row in the same resultset without the use of self-join . LAG is a analytical function in a select statement to compare values in the current row with values in a previous row.

Create Table Sales( empid int, Year int, sales money)

insert into Sales values(1,2000,100)

insert into Sales values(1,2001,200)

insert into Sales values(1,2002,300)

insert into Sales values(2,2001,200)

insert into Sales values(2,2002,300)

--Previous row
 SELECT empid, Year, sales,

LAG(sales, 1, 0) OVER (PARTITION BY empid ORDER BY Year) AS PrevProfit FROM Sales

Results:
empid Year sales PrevProfit

----------- ----------- --------------------- ---------------------

1 2000 100.00 0.00

1 2001 200.00 100.00

1 2002 300.00 200.00

2 2001 200.00 0.00

2 2002 300.00 200.00

 
Two rows earlier


SELECT empid, Year, sales,

LAG(sales, 2, 0) OVER (PARTITION BY empid ORDER BY Year) AS PrevProfit FROM Sales
 Results :

empid Year sales PrevProfit

----------- ----------- --------------------- ---------------------

1 2000 100.00 0.00

1 2001 200.00 0.00

1 2002 300.00 100.00

2 2001 200.00 0.00

2 2002 300.00 0.00

--YOY Profit

SELECT empid, Year, sales,

LAG(sales, 1, 0) OVER (PARTITION BY empid ORDER BY Year) AS PrevProfit,

sales-LAG(sales, 1, 0) OVER (PARTITION BY empid ORDER BY Year) as YOY FROM Sales
 
Results :


empid Year sales PrevProfit YOY

----------- ----------- --------------------- --------------------- ---------------------

1 2000 100.00 0.00 100.00

1 2001 200.00 100.00 100.00

1 2002 300.00 200.00 100.00

2 2001 200.00 0.00 200.00

2 2002 300.00 200.00 100.00

List of Tables and Stored procedure created recently

select *

from sys.objects

where (type = 'U' or type = 'P')

and create_date > dateadd(m, -3, getdate())

order by create_date desc