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)=1) THEN 'All_Tables'
      ELSE ISNULL(sob.name, 'unknown') END AS Table_name,
      SUM(sys.length) AS Byte_Length
FROM sysobjects sob, syscolumns 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

In this post, let us see how to remove duplicate records using common table expression.
Another example in this post, shows how to remove duplicate values in the delimited string.

Example 1:

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