T-SQL Snippets, Codes,Tweaks

Operators Allowed in the WHERE Clause
= Equal
<> Not equal
> Greater than
<>= Greater than or equal
<= Less than or equal BETWEEN Between an inclusive range LIKE Search for a pattern IN If you know the exact value you want to return for at least one of the columns

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

Taking a backup of a table,
The SQL SELECT INTO statement can be used to create backup copies of tables.

SELECT *INTO new_table_name [IN externaldatabase] FROM old_tablename

or

SELECT column_name(s)INTO new_table_name [IN externaldatabase]

FROM old_tablename

or

Here it creates the table name DEF and copies the content accordingly
SELECT COLA,COLB,COLC,COLD,COLE,COLF INTO DEF FROM ABC

or

SELECT * INTO DEF FROM ABC

or

We can also use the IN clause to copy the table into another database:

SELECT * INTO Persons_Backup IN 'Backup.mdb' FROM Persons

or

We can also copy only a few fields into the new table:

SELECT LastName,FirstName INTO Persons_Backup FROM Persons

or

SQL SELECT INTO - With a WHERE ClauseWe can also add a WHERE clause.

The following SQL statement creates a "Persons_Backup" table with only the persons who lives in the city "Sandnes":

SELECT LastName,Firstname INTO Persons_Backup FROM Persons WHERE

City='Sandnes'

or

SQL SELECT INTO - Joined TablesSelecting data from more than one table is also possible.
The following example creates a "Persons_Order_Backup" table contains data from the two tables "Persons" and "Orders":

SELECT Persons.LastName,Orders.OrderNo INTO Persons_Order_Backup FROM Persons INNER JOIN Orders ON Persons.P_Id=Orders.P_Id

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

Exporting the output into a text or csv file:

Syntax:
sqlcmd -S SERVERNAME -i C:\1.sql -o C:\hello.txt or [C:\hello.csv]

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

Executing select statements in bcp

The below code, iam copying the output into a directory with , delimited values
bcp "select * from [DATABASENAME]..[TABLENAME]" queryout C:\Output.csv -S SERVERNAME -c -t',' -T

NOTE: The two .. are must.

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

Filter Out unmatching records between two tables

SELECT COLA FROM ABC A WHERE NOT EXISTS (SELECT * FROM DEF D WHERE (A.COLA=D.ABC))

or

SELECT COLA FROM DEF D WHERE NOT EXISTS (SELECT * FROM ABC A WHERE (D.COLA=A.ABC))

or

SELECT A.COLA from ABC A where A.COLA NOT IN (select D.COLA from DEF D)

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

Removing 0(zeros) from the begining of a particular number COLA is a column name and ABC is the table name

select REPLACE(LTRIM(REPLACE(COLA, '0', ' ')), ' ', '0') from ABC

or

Select Replace(T.Col1,”@”, ‘at’) As [ColName] From MyTable T

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

Retrieve MSSQL table information

select * from information_schema.columnswhere table_name= 'ABC'

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

To Query the list of columns in a table

select column_name 'Column Name', data_type 'Data Type', character_maximum_length 'Maximum Length' from information_schema.columns where table_name = 'master'

or

SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.ColumnsWHERE TABLE_NAME = 'master'

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

To find the name of the table in which a particular column name exists.

Here it checks for the database for the tables which have the column name as COLA

SELECT name FROM sysobjects WHERE id IN ( SELECT id FROM syscolumns WHERE name = 'ABC' )

or

if you're unsure exactly what the column is named, but you suspect you know part of the name, then try...

SELECT name FROM sysobjects WHERE id IN(SELECT id FROM syscolumns WHERE name like '%COLA%')

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

To query the stored procedures in a table

select object_name(id) as objectname from syscommentswhere text like '%ABC%'

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

Finding a text in stored procedure

SELECT so.[name] AS 'storedProcedure' FROM sysobjects so JOIN syscomments sc ONso.[id] = sc.[id]WHERE so.[type] = 'P'AND sc.[text] LIKE '%action%'

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

checking the column if it exists and add

IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'ABC' AND COLUMN_NAME = 'COLA')

BEGIN

ALTER TABLE ABC ADD COLA int

END

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

Renaming a column name

EXEC sp_rename 'TABLENAME.OLD_COLUM

NNAME', 'NEW_COLUMNAME', 'COLUMN'

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

To get the columns names

SELECT table_name=sysobjects.name,column_name=syscolumns.name, datatype=systypes.name, length=syscolumns.length FROM sysobjects JOIN syscolumns ON sysobjects.id = syscolumns.id JOIN systypes ON syscolumns.xtype=systypes.xtype WHERE sysobjects.xtype='U'and syscolumns.NAME = 'assign the name of the column here'

ORDER BY sysobjects.name,syscolumns.colid

or

SELECT table_name=sysobjects.name,column_name=syscolumns.name,

datatype=systypes.name,length=syscolumns.length

FROM sysobjects JOIN syscolumns ON sysobjects.id = syscolumns.id JOIN systypes

ON syscolumns.xtype=systypes.xtype WHERE syscolumns.name='COLA'

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

To split a column into two using a SELECT statement based on a condition

SELECT CASE WHEN STATE = 'VA'

THEN STATE ELSE '' END as 'Higher income group',

CASE WHEN STATE = 'NY'

THEN STATE ELSE '' END as 'Lower income group',NUMBER FROM ABC

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

case query

SELECT NUMBER, STATE, priority = ( CASE STATE when 'WA' then 1 when 'VA' then 2 when 'NY' then 3 when 'CA' then 4 end) FROM ABC ORDER BY state

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

To find out the user name at SQL prompt.

Select user_name()

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

If the table exists in the database

select name from sysobjects where xtype='u' and name = 'master'

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

Determining Your Session IDs for SQL Serversession id

SELECT @@SPID

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

No of tables count in a database

SELECT COUNT(*) from information_schema.tables WHERE table_type = 'base table'

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

list of table names

SELECT TABLE_NAME from information_schema.tables WHERE table_type = 'base table' order by table_name

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

Returns list of tables that have no primary key

select name from sysobjects where id not in (select b.id from sysconstraints b, sysobjects c where c.type = 'K' and c.id = b.constid) and type = 'U' order by name

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

SQL Query to Update a Column Value in All Tables

SELECT 'UPDATE ',RTRIM(Name),' SET UserID=2 WHERE UserID=1' FROM sysobjectsWHERE Type='U'

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

Displaying structure of a table

SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_CATALOG = 'DATABASENAME' AND TABLE_NAME = 'ABC'

or

exec sp_columns ABC

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

To determine if a table exists in a SQL Server Database

IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE='BASE TABLE' AND TABLE_NAME='tablename') SELECT 'tablename exists.' ELSE SELECT 'tablename does not exist.'

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

How do I find all the foreign keys in a database?

SELECT FK_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

PS:optional: ORDER BY 1,2,3,4

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

The statement takes two tables (ABC and DEF), and figures out which columns between the two are similar:

SELECT table_name=sysobjects.name,column_name=syscolumns.name, datatype=systypes.name,length=syscolumns.length FROM sysobjects JOIN syscolumns ON sysobjects.id = syscolumns.id JOIN systypes ON syscolumns.xtype=systypes.xtype WHERE (sysobjects.xtype='U' OR sysobjects.xtype='v') AND syscolumns.name IN (SELECT syscolumns.name FROM sysobjects JOIN syscolumns ON sysobjects.id = syscolumns.id JOIN systypes ON syscolumns.xtype=systypes.xtype WHERE sysobjects.name = 'ABC') AND sysobjects.name = 'DEF'

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

How to find a text inside SQL Server procedures / triggers?

SELECT DISTINCT LEFT(so.name, 100) AS Object_Name,"object_type"= left(case so.type when 'U' then 'Table - User'

when 'S' then 'Table - System'

when 'V' then 'Table - View'

when 'TR' then 'Trigger'

when 'P' then 'Stored Procedure'

when 'C' then 'Constraint - Check'

when 'D' then 'Default'

when 'K' then 'Key - Primary'

when 'F' then 'Key - Foreign'

when 'L' then 'Log'

when 'R' then 'Rule'

when 'RF' then 'Replication Filter stp'

else '<>'

end -- case so.type ,25)

FROM syscomments sc

INNER JOIN sysobjects so

ON so.id = sc.id WHERE text Like '%empname%' ORDER BY 2,1

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

GETTING VALUES ROW BY ROW

DECLARE @cursor CURSOR
SET @cursor=CURSOR FOR SELECT * FROM abc
OPEN @cursorFETCH @cursor
WHILE (@@FETCH_STATUS=0) BEGIN FETCH @cursorEND
CLOSE @cursorDEALLOCATE @cursor

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

NO OF WAITING TASKS

SELECT COUNT(*) AS 'Number of waiting tasks'FROM sys.dm_os_waiting_tasks

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

Number of threads used with MSSQL 2005

SELECT COUNT(*) AS 'Number of threads'FROM sys.dm_os_waiting_tasks

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

Type of waiting tasks with MSSQL 2005

SELECT CAST(wait_type AS VARCHAR(30)) AS 'Waiting task',COUNT (*) AS 'Number of waiting tasks 'FROM sys.dm_os_waiting_tasks GROUP BY wait_type ORDER BY 'Number of waiting tasks' DESC WHERE wait_type <> 'THREADPOOL'

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

Resource Bottlenecks with MSSQL 2005

SELECT resource_address AS 'Resource Bottleneck',COUNT (*) AS '# of bottlenecks' FROM sys.dm_os_waiting_tasks WHERE resource_address <> 0 GROUP BY resource_address ORDER BY '# of bottlenecks' DESC

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

IO Bottlenecks with MSSQL 2005

SELECT waiting_task_address AS 'Task address', session_id AS 'Session', exec_context_id AS 'Context', wait_duration_ms AS 'Wait in millsec',CAST(wait_type AS VARCHAR(30)) AS 'Type', resource_address AS 'Resource address', blocking_task_address AS 'Blocking task', blocking_session_id AS 'Blocking session',CAST(resource_description AS VARCHAR(30)) AS 'Resource' FROM sys.dm_os_waiting_tasks WHERE wait_duration_ms > 20 AND wait_type LIKE '%PAGEIOLATCH%'

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

Has a row been changed

SELECT UNIQ_ID,CD_TYPE,CODE1,CHECKSUM(UNIQ_ID,CD_TYPE,CODE1) AS 'Checksum specific',CHECKSUM(*) AS 'Checksum general' FROM INFO

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

Create a comma delimited list (csv) of unique values

DECLARE @uniquelist VARCHAR(1000);SELECT @uniquelist = LTRIM(COALESCE(@uniquelist+',' , ' ')) + CAST(UNIQ_ID AS VARCHAR(10))FROM (SELECT DISTINCT UNIQ_ID FROM INFO) ABC SELECT @uniquelist AS 'Unique emp_short list: comma delimited'

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

Incrementing counters that already contain NULLs

SELECT counter,CASE WHEN counter IS null THEN (row_number() OVER (ORDER BY emp_short,trans_date) +(SELECT MAX(trmax.counter) FROM trans02 trmax ) + 10 ) ELSE counter END AS 'Calculated counter', emp_short,trans_date,debit,credit FROM trans02ORDER BY 'Calculated counter'

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

Adding a link server

EXEC master.dbo.sp_addlinkedserver @server = N'SERVERNAME', @srvproduct=N'SQL Server'

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

Copying the table structure without copying the data from another table

SELECT * INTO NewTable FROM abc WHERE 1=2

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

Renaming the table name and column name

EXEC sp_rename 'OldTableName', 'NewTableName'

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

Renaming Column name Syntax

EXEC sp_rename @objname = 'TableName.OldColumnName', @newname = 'NewColumnName', @objtype = 'COLUMN'

Example : XYZ and ZZZ and DEF are table namesexec sp_rename 'XYZ' , 'ZZZ' - Renaming XYZ table name to ZZZexec sp_rename 'DEF.[B]', 'GROUP' 'COLUMN' - Renaming Column B of table DEF to , GROUP

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

This script will gives row number for every table in database.
select OBJECT_NAME(object_id) TableName,st.row_countfrom sys.dm_db_partition_stats st where index_id<2>

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

Obtaining CLR Execution performance counter values.

SELECT object_name, counter_name, cntr_value, cntr_type
FROM sys.dm_os_performance_counters
WHERE counter_name LIKE '%CLR%'

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

Query performance and time spent in SQL CLR.

SELECT (SELECT text FROM sys.dm_exec_sql_text(qs.sql_handle)) AS query_text, qs.*FROM sys.dm_exec_query_stats AS qs WHERE qs.total_clr_time > 0 ORDER BY qs.total_clr_time desc

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

Requests that are currently in SQL CLR

SELECT session_id, request_id, start_time, status, command, database_id,
wait_type, wait_time, last_wait_type, wait_resource, cpu_time,
total_elapsed_time, nest_level, executing_managed_code
FROM sys.dm_exec_requests WHERE executing_managed_code = 1

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

SQL CLR Wait Statistics

SELECT ws.* FROM sys.dm_os_wait_stats AS wsWHERE ws.wait_type LIKE '%clr%'

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

User-defined Types

User Defined Type Metadata

Includes base assembly information

SELECT st.[name] AS [Type Name] , st.max_length, st.[precision], st.scale, st.collation_name, st.is_nullable, sa.create_date, sa.[name] AS [Assembly Name], sa.permission_set_desc AS [Access]FROM sys.types AS st INNER JOIN sys.type_assembly_usages AS tau ON st.user_type_id = tau.user_type_id INNER JOIN sys.assemblies AS sa ON tau.assembly_id = sa.assembly_id

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

LIST CLR AGGREGATE FUNCTIONS

SELECT schema_name(so.schema_id) + N'.' + so.[name] AS [Name] , so.create_date, so.modify_date, sa.permission_set_desc AS [Access]FROM sys.objects AS so INNER JOIN sys.module_assembly_usages AS mau ON so.object_id = mau.object_id INNER JOIN sys.assemblies AS sa ON mau.assembly_id = sa.assembly_idWHERE so.type_desc = N'AGGREGATE_FUNCTION'

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

LIST CLR TABLE FUNCTIONS

SELECT schema_name(so.schema_id) + N'.' + so.[name] AS [Name], so.create_date, so.modify_date, sa.permission_set_desc AS [Access] FROM sys.objects AS so INNER JOIN sys.module_assembly_usages AS sau ON so.object_id = sau.object_id INNER JOIN sys.assemblies AS sa ON sau.assembly_id = sa.assembly_id WHERE so.type_desc = N'CLR_TABLE_VALUED_FUNCTION'

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

LIST CLR FUNCTIONS:

SELECT schema_name(so.schema_id) + N'.' + so.[name] AS [Name], so.create_date, so.modify_date, sa.permission_set_desc AS [Access] FROM sys.objects AS so INNER JOIN sys.module_assembly_usages AS sau ON so.object_id = sau.object_id INNER JOIN sys.assemblies AS sa ON sau.assembly_id = sa.assembly_id WHERE so.type_desc = N'CLR_SCALAR_FUNCTION'

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

Substring function

Select SUBSTRING(COLA,5,10) as NAME,SUBSTRING(COLA,152,2 )as CUSTNAME from ABC

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

Getting all the rows in one column

declare @res varchar(4000)

set @res =''

select @res = @res + A + ',' from testing

if @@rowcount > 0 select substring(@res, 1, len(@res)-1)
NOTE:Testing is the table name and 'A' is the column name

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

List of databases attached

SELECT dtb.name AS [Name] FROM master.dbo.sysdatabases AS dtb ORDER BY [Name] ASC

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

Adding strings to the output

select (CHAR('39')+COLA+CHAR('39')++CHAR('44')) from ABC

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

Generating a script using the existing table structure

SELECT CASE WHEN colid = 1 THEN 'CREATE TABLE ' + O.name + ' (' ELSE '' END+ C.name + ' ' + CASE C.xtype WHEN 167 THEN 'varchar(' + CONVERT(varchar(20), C.length) + ') 'WHEN 61 THEN 'datetime'-- ...ELSE 'UnknownType_' + CONVERT(varchar(20), C.xtype)END + ' '+ CASE WHEN C.isnullable = 1THEN 'NULL' ELSE 'NOT NULL' END+ CASE WHEN colid =(SELECT MAX(colid) FROM dbo.syscolumns C2 WHERE C2.id = O.id) THEN ')' ELSE ',' ENDFROM dbo.sysobjects O JOIN dbo.syscolumns C ON C.id = O.id WHERE O.name= 'info' AND O.type = 'U'ORDER BY colid