SQL Code

Sunday, September 16, 2018

Set Identity and Reseed

/*************************************************

SET IDENTITY + RESEED

***************************************************/

DBCC CHECKIDENT ('[DB].[SCHEMA].[TABLE]', RESEED, 0);

/******

Although most answers are suggesting RESEED to 0, But many a times we need to just reseed to next Id available

******/

declare @max int

select @max=max([Id])from [TestTable]

if @max IS NUll //check when max is returned as null

SET @max = 0

DBCC CHECKIDENT ('[TestTable]', RESEED,@max)

SP_WHO2 With a Order by Clause & Monitor Server

/*************************************************

SP_WHO 2 ORDER BY

***************************************************/

CREATE TABLE #sp_who2 (SPID INT,Status VARCHAR(255),

BlkBy VARCHAR(255),DBName VARCHAR(255),

Command VARCHAR(255),CPUTime INT,

DiskIO INT,LastBatch VARCHAR(255),

ProgramName VARCHAR(255),SPID2 INT,

REQUESTID INT)

INSERT INTO #sp_who2 EXEC sp_who2

SELECT *

FROM #sp_who2

WHERE DBName <> 'master' -- Add any filtering of the results here

ORDER BY status ASC -- Add any sorting of the results here

DROP TABLE #sp_who2

/*************************************************

Running Queries

***************************************************/

SELECT p.spid

, p.status

, p.hostname

, p.loginame

, p.cpu

, r.start_time

, r.command

, p.program_name

, text

FROM sys.dm_exec_requests AS r,

master.dbo.sysprocesses AS p

CROSS APPLY sys.dm_exec_sql_text(p.sql_handle)

WHERE p.status NOT IN ('sleeping', 'background')

AND r.session_id = p.spid

/*************************************************

All Queries

***************************************************/

SELECT sqltext.TEXT

, req.session_id

, req.status

, req.command

, req.cpu_time

, req.total_elapsed_time

FROM sys.dm_exec_requests req

CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sqltext

Pulling Data From A Error Log

/*************************************************

PULLING DATA FROM THE ERROR LOG

**************************************************/

IF OBJECT_ID('IOErrorLog') IS NULL

CREATE TABLE IOErrorLog (

ServerName nvarchar(256),

LogFileName nvarchar(4000),

Occurances int,

LogDate datetime,

ProcessInfo nvarchar(50),

ErrorText nvarchar(max),

CONSTRAINT pk_IOErrorLog PRIMARY KEY (ServerName, LogDate, LogFileName)

)

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

-- This part of the code was found here:

-- https://ask.sqlservercentral.com/questions/99484/number-of-error-log-files.html

DECLARE @FileList AS TABLE (

subdirectory NVARCHAR(4000) NOT NULL

,DEPTH BIGINT NOT NULL

,[FILE] BIGINT NOT NULL

);

DECLARE @ErrorLog NVARCHAR(4000), @ErrorLogPath NVARCHAR(4000);

SELECT @ErrorLog = CAST(SERVERPROPERTY(N'errorlogfilename') AS NVARCHAR(4000));

SELECT @ErrorLogPath = SUBSTRING(@ErrorLog, 1, LEN(@ErrorLog) - CHARINDEX(N'\', REVERSE(@ErrorLog))) + N'\';

INSERT INTO @FileList

EXEC xp_dirtree @ErrorLogPath, 0, 1;

DECLARE @NumberOfLogfiles INT;

SET @NumberOfLogfiles = (SELECT COUNT(*) FROM @FileList WHERE [@FileList].subdirectory LIKE N'ERRORLOG%');

-- SELECT @NumberOfLogfiles;

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

CREATE TABLE #temp (

LogDate datetime,

ProcessInfo nvarchar(500),

ErrorText nvarchar(max))

DECLARE @p1 INT = 0

WHILE @p1 < @NumberOfLogfiles

BEGIN

-- P1 is the file number starting at 0

DECLARE

@p2 INT = 1,

-- P2 1 for SQL logs, 2 for SQL Agent logs

@p3 NVARCHAR(255) = 'occurrence',

-- P3 is a value to search on

@p4 NVARCHAR(255) = 'i/o'

-- P4 is another search value

BEGIN TRY

INSERT INTO #temp

EXEC sys.xp_readerrorlog @p1,@p2,@p3,@p4

END TRY

BEGIN CATCH

PRINT 'Error occurred processing file ' + cast(@p1 as varchar(10))

END CATCH

SET @p1 = @p1 + 1

END

INSERT INTO IOErrorLog

SELECT DISTINCT @@SERVERNAME,

SUBSTRING(ErrorText,charindex('[',ErrorText)+1,

charindex(']',ErrorText)-charindex('[',ErrorText)-1),

SUBSTRING(ErrorText, 27, patindex('%occurrence%',ErrorText)-28),

LogDate, ProcessInfo, ErrorText

FROM #temp

WHERE #temp.LogDate NOT IN (

SELECT LogDate

FROM IOErrorLog

WHERE ServerName = @@SERVERNAME)

DROP TABLE #temp

SELECT * FROM IOErrorLog

Tuesday, September 11, 2018

Checks the disk space

USE [databasename]
GO

EXEC MASTER..xp_fixeddrives

/***********************************************

************************************************/

SELECT '['+ SCHEMA_NAME(A.schema_id)
+ '].[' + A.Name + ']' AS [TABLE]
, SUM(B.rows) AS [RowCount]
FROM sys.objects A
JOIN sys.partitions B ON A.object_id = B.object_id
WHERE A.type = 'U'
-- AND SCHEMA_NAME(A.schema_id) = 'OTH'
GROUP BY A.schema_id, A.Name
ORDER BY 2 desc
GO

/***********************************************

************************************************/

SELECT
t.NAME AS TableName,
s.Name AS SchemaName,
p.rows AS RowCounts,
SUM(a.total_pages) * 8 AS TotalSpaceKB,
SUM(a.used_pages) * 8 AS UsedSpaceKB,
(SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB
FROM sys.tables t
JOIN sys.indexes i
ON t.OBJECT_ID = i.object_id
JOIN sys.partitions p
ON i.object_id = p.OBJECT_ID
AND i.index_id = p.index_id
JOIN sys.allocation_units a
ON p.partition_id = a.container_id
LEFT JOIN sys.schemas s
ON t.schema_id = s.schema_id
WHERE t.NAME NOT LIKE 'dt%'
AND t.is_ms_shipped = 0
AND i.OBJECT_ID > 255
GROUP BY t.Name, s.Name, p.Rows
ORDER BY 4 DESC, t.Name

GO

/***********************************************

************************************************/

------------------ All Db Sizes
with fs
as
(
select database_id, [type], size * 8.0 / 1024 size
from sys.master_files
)
select name,
(select sum(size) from fs where [type] = 0 and fs.database_id = db.database_id) DataFileSizeMB,
(select sum(size) from fs where [type] = 1 and fs.database_id = db.database_id) LogFileSizeMB
from sys.databases db
order by 3 desc ,2 desc

Check for Tables used in StoreProcedures

Check if there is a specific table that is used in the store procedure

USE [databasename]
GO
DECLARE @Tablename VARCHAR(255) = 'tablename'

SELECT DISTINCT so.name
FROM syscomments sc
JOIN sysobjects so
ON sc.id = so.id
WHERE sc.[TEXT] LIKE '%' + @Tablename + '%'

----Option 2
SELECT DISTINCT o.name, o.xtype
FROM syscomments c
JOIN sysobjects o
ON c.id = o.id
WHERE c.[TEXT] LIKE '%' + @Tablename + '%'

Check Foreign Key Constraints

Checks a table and all the Foreign keys constraints on that table.

SELECT o1.name AS Referencing_Object_name
, c1.name AS referencing_column_Name
, o2.name AS Referenced_Object_name
, c2.name AS Referenced_Column_Name
, s.name AS Constraint_name

FROM sysforeignkeys fk
JOIN sysobjects o1
ON fk.fkeyid = o1.id
JOIN sysobjects o2
ON fk.rkeyid = o2.id
JOIN syscolumns c1
ON c1.id = o1.id AND c1.colid = fk.fkey
JOIN syscolumns c2
ON c2.id = o2.id AND c2.colid = fk.rkey
JOIN sysobjects s
ON fk.constid = s.id
AND o2.name = 'table'

Find / Update / Delete Duplicated Rows in a table with Columns

if you need to update / delete / get rows that contains duplicated rows, this uses a CTE table to get the data and then either DELETE / UPDATE or SELECT these columns.

; WITH CTE AS
(
SELECT rn = ROW_NUMBER()
OVER(
PARTITION BY [duplicatedColumn01], [duplicatedColumn02]
ORDER BY [duplicatedColumn01] desc), *
FROM [dbo].[TableWithDuplicates] cp

)

/*******************************************************************
DELETE
FROM cte
WHERE rn > 1
******************************************************************/

/*******************************************************************
UPDATE cte
SET [Column01] = 0
FROM cte
WHERE rn > 1
*******************************************************************/

SELECT *
FROM cte
WHERE rn > 1