Search

TSQL script - to identify all the columns with specific datatype in specific database

SELECT OBJECT_NAME(C.OBJECT_ID) TableName, C.name ColumnName
FROM sys.columns AS C
JOIN sys.types AS T ON C.user_type_id=T.user_type_id
WHERE T.name = 'INT' --you can change to other datatypes

ORDER BY C.OBJECT_ID;
GO

TSQL script - List All Stored Procedure Modified in Last N Days

SELECT name
FROM sys.objects
WHERE type = 'P'
AND DATEDIFF(D,modify_date, GETDATE()) < N

TSQL script - to Display Foreign Key Relationships and Name of the Constraint for Each Table in Database

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







Reference : Pinal Dave (http://blog.SQLAuthority.com)

TSQL script - to Find ByteSize of All the Tables in Database

SELECT CASE WHEN (GROUPING(sob.name)=1THEN 'All_Tables'
     
ELSE ISNULL(sob.name'unknown'END AS Table_name
,
     
SUM(sys.lengthAS 
Byte_Length

FROM sysobjects sobsyscolumns sys
WHERE sob.xtype='u' AND sys.id=sob.id
GROUP BY sob.name
WITH CUBE



Reference : Pinal Dave (http://blog.SQLAuthority.com)

TSQL Script - to Find Details About TempDB

SELECT name AS FileName,

size*1.0/128 AS FileSizeinMB,

CASE max_size WHEN 0 THEN 'Autogrowth is off.'
              WHEN -1 THEN 'Autogrowth is on.'
              ELSE 'Log file will grow to a maximum size of 2 TB.'
              END AutogrowthStatus,

growth AS 'GrowthValue',

'GrowthIncrement' = CASE WHEN growth = 0 THEN 'Size is fixed and will not grow.'

WHEN growth > 0AND is_percent_growth = 0 THEN 'Growth value is in 8-KB pages.'

ELSE 'Growth value is a percentage.'END
FROM tempdb.sys.database_files;

GO





Reference : Pinal Dave (http://blog.SQLAuthority.com)

Sqlserver TRIM() Function – Remove Trailing Spaces, Leading Spaces, White Space, Tabs, Carriage Returns, Line Feeds

CREATE FUNCTION dbo.LTrimX(@str VARCHAR(MAX))
RETURNS VARCHAR(MAX)
AS
BEGIN
DECLARE
@trimchars VARCHAR(10
)

SET @trimchars = CHAR(9)+CHAR(10)+CHAR(13)+CHAR(32)
IF @str LIKE '[' + @trimchars + ']%'
SET @str = SUBSTRING(@str, PATINDEX('%[^' + @trimchars + ']%', @str), 8000)
RETURN @str
END
GO

CREATE FUNCTION dbo.RTrimX(@str VARCHAR(MAX))
RETURNS VARCHAR(MAX)
AS
BEGIN
DECLARE
@trimchars VARCHAR(10
)

SET @trimchars = CHAR(9)+CHAR(10)+CHAR(13)+CHAR(32)
IF @str LIKE '%[' + @trimchars + ']'
SET @str = REVERSE(dbo.LTrimX(REVERSE(@str)))
RETURN @str
END
GO

CREATE FUNCTION dbo.TrimX(@str VARCHAR(MAX))
RETURNS VARCHAR(MAX)
AS
BEGIN
RETURN

dbo.LTrimX(dbo.RTrimX(@str))
END
GO
/* Run the created function */
SELECT dbo.TRIMX('         word leading trailing spaces           ') AS 'TrimmedWord'
GO 

 Reference : Pinal Dave (http://blog.SQLAuthority.com)

Sqlserver - Address Windowing Extensions (AWE)

AWE is used by SQL Server when it has to support very large amounts of physical memory.
AWE feature is only available in SQL Server Enterprise, Standard, and Developer editions with of SQL Server 32 bit version.

TSQL script - To List Primary Keys and Foreign Keys of Database

SELECT OBJECT_NAME(OBJECT_ID) AS ConstraintName,
SCHEMA_NAME(schema_id) AS SchemaName,

OBJECT_NAME(parent_object_id) AS TableName,
type_desc   AS ConstraintType
FROM SYS.OBJECTS
WHERE type_desc IN ('FOREIGN_KEY_CONSTRAINT','PRIMARY_KEY_CONSTRAINT')
GO


To Get a List of Fixed Hard Drive and Free Space on Server

EXEC master..xp_fixeddrives

Migrating DTS Packages to Integration Services

http://msdn.microsoft.com/en-us/library/cc768544.aspx

TSQL script - To Find Most Expensive Queries

SELECT TOP 10 SUBSTRING(qt.TEXT, (qs.statement_start_offset/2)+1,
((
CASE
qs.statement_end_offset WHEN -1 THEN DATALENGTH(qt.TEXT)

ELSE qs.statement_end_offset END - qs.statement_start_offset)/2)+1),
qs.execution_count,
qs.total_logical_reads,
qs.last_logical_reads,
qs.total_logical_writes,
qs.last_logical_writes,
qs.total_worker_time,
qs.last_worker_time,
qs.total_elapsed_time/1000000 total_elapsed_time_in_S,
qs.last_elapsed_time/1000000 last_elapsed_time_in_S,
qs.last_execution_time,
qp.query_plan
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
ORDER BY qs.total_logical_reads DESC





Reference : Pinal Dave (http://blog.SQLAuthority.com)

Sqlserver - Page Life Expectancy

Page Life Expectancy is number of seconds a page will stay in the buffer pool without references.

SELECT
[object_name],
[counter_name],
[cntr_value]
FROM sys.dm_os_performance_counters
WHERE [object_name] LIKE '%objectname%'
AND [counter_name] = 'Page life expectancy'

Sqlserver - suspect_pages Table

The suspect_pages table is used for maintaining information about suspect pages, and is relevant in helping to decide whether a restore is necessary. The suspect_pages table resides in the msdb database and was introduced in SQL Server 2005.
A page is considered "suspect" when the SQL Server Database Engine encounters one of the following errors when it tries to read a data page:
  • An 823 error that was caused by a cyclic redundancy check (CRC) issued by the operating system, such as a disk error (certain hardware errors)
  • An 824 error, such as a torn page (any logical error)
The page ID of every suspect page is recorded in the suspect_pages table. The Database Engine records any suspect pages encountered during regular processing, such as the following:
  • A query has to read a page.
  • During a DBCC CHECKDB operation.
  • During a backup operation.

TSQL tip - Finding Apostrophes in String and Text

To find   Apostrophes ,use the following tip :

CREATE TABLE Employee (Firstname VARCHAR(10))

INSERT  INTO Employee VALUES ('SATHYA')

INSERT INTO Employee VALUES ('DEEPAK ''s')

SELECT * FROM Employee WHERE Firstname like '%''%'


--DROP TABLE Employee

SQL Server sees everything after that second single quote as an error in your SQL code.




TSQL script- To get database restore history

USE MSDB;
GO
 SELECT
DBRestored = destination_database_name,
RestoreDate = restore_date,
SourceDB = b.database_name,
SourceFile = physical_name,
BackupDate = backup_start_date
FROM RestoreHistory h
INNER JOIN BackupSet b
ON h.backup_set_id = b.backup_set_id
INNER JOIN BackupFile f
ON f.backup_set_id = b.backup_set_id
ORDER BY RestoreDate

  

TSQL script - CTE to remove duplicate rows


CREATE TABLE #Table (C1 INT,C2 VARCHAR(10))

INSERT INTO #Table VALUES (1,'SQL Server')
INSERT INTO #Table VALUES (1,'SQL Server')
INSERT INTO #Table VALUES (2,'Oracle')

SELECT * FROM #Table

;WITH Delete_Duplicate_Row_cte
     AS (SELECT ROW_NUMBER()OVER(PARTITION BY C1, C2 ORDER BY C1,C2) ROW_NUM,*
         FROM   #Table )
DELETE FROM Delete_Duplicate_Row_cte WHERE  ROW_NUM > 1

SELECT * FROM #Table

--DROP TABLE #Table


--Example:


DECLARE @TMP1 TABLE (ID INT ,Column1 VARCHAR(100))
DECLARE @TMP TABLE (ID INT IDENTITY(1,1),Column1 VARCHAR(100))
INSERT INTO @TMP SELECT 'Oracle|Oracle|SQL Server'
INSERT INTO @TMP SELECT 'DB2|DB2|SQL Server'

--splitting

;WITH cte AS (
    SELECT
        ID,
        CAST('<i>' + REPLACE(Column1, '|', '</i><i>') + '</i>' AS XML) AS Column1
    FROM @TMP
)
INSERT INTO @TMP1
SELECT
    ID,
    x.i.value('.', 'VARCHAR(10)')
FROM cte
CROSS APPLY Column1.nodes('//i') x(i)

--Deleting duplicate

;WITH Delete_Duplicate_Row
     AS (SELECT ROW_NUMBER()OVER(PARTITION BY ID,Column1 ORDER BY Column1) ROW_NUM,*
         FROM   @TMP1 )
DELETE FROM Delete_Duplicate_Row WHERE  ROW_NUM > 1

--Concatenating

SELECT MAX(ID) ID,
       STUFF((SELECT ' | ' + Column1
              FROM   @TMP1 X WHERE X.ID = Y.ID GROUP  BY ID,Column1
              FOR XML PATH('')), 1, 2, '') CSV               
FROM   @TMP1 Y
GROUP  BY ID