USefull commands in sql server query

Command to check who are using databases with which users ids and more

sp_who2

To Kill the specific process for the Actice Connection use below command

kill 59


To take The back up for the Database

backup database test to disk='foldername'

SQL Server Date Time Format

SQL Server Date Time Format


In SQL Server used Cast or Convert function to Format DateTime value or column into a specific date format.Both function are used to convert datetime to varchar or string.

CAST function Syntax: CAST(expression as data_type)

Let's convert current date time to varchar

select cast(getdate() as varchar)

CONVERT function is used to change or convert the DateTime formats.By using convert function you can get only Date part or only Time part from the datetime.

CONVERT Function Syntax: CONVERT(data_type,expression,date Format style)

Let's take Sql Server DateTtime styles example:

Format

Query

USA mm/dd/yy

select convert(varchar, getdate(), 1)

ANSI yy.mm.dd

select convert(varchar, getdate(), 2)

British/French dd/mm/yy

select convert(varchar, getdate(), 3)

German dd.mm.yy

select convert(varchar, getdate(), 4)

Italian dd-mm-yy

select convert(varchar, getdate(), 5)

dd mon yy

select convert(varchar, getdate(), 6)

Mon dd, yy

select convert(varchar, getdate(), 7)

USA mm-dd-yy

select convert(varchar, getdate(), 10)

JAPAN yy/mm/dd

select convert(varchar, getdate(), 11)

ISO yymmdd

select convert(varchar, getdate(), 12)

mon dd yyyy hh:miAM (or PM)

select convert(varchar, getdate(), 100)

mm/dd/yyyy

select convert(varchar, getdate(), 101)

yyyy.mm.dd

select convert(varchar, getdate(), 102)

dd/mm/yyyy

select convert(varchar, getdate(), 103)

dd.mm.yyyy

select convert(varchar, getdate(), 104)

dd-mm-yyyy

select convert(varchar, getdate(), 105)

dd mon yyyy

select convert(varchar, getdate(), 106)

Mon dd, yyyy

select convert(varchar, getdate(), 107)

hh:mm:ss

select convert(varchar, getdate(), 108)

Default + milliseconds mon dd yyyy hh:mi:ss:mmmAM (or PM)

select convert(varchar, getdate(), 109)

mm-dd-yyyy

select convert(varchar, getdate(), 110)

yyyy/mm/dd

select convert(varchar, getdate(), 111)

yyyymmdd

select convert(varchar, getdate(), 112)

Europe default + milliseconds dd mon yyyy hh:mm:ss:mmm(24h)

select convert(varchar, getdate(), 113) or select convert(varchar, getdate(), 13)

hh:mi:ss:mmm(24h)

select convert(varchar, getdate(), 114)

How to use dynamic table name and execute the Query in sql server query

ECLARE @tbl sysname,
@sql nvarchar(8000),
@params nvarchar(8000),
@count int



SELECT @sql =
N' SELECT @cnt = COUNT(*) FROM dbo.' + quotename(@tbl) +
N' WHERE LastUpdated BETWEEN @fromdate AND ' +
N' coalesce(@todate, ''99991231'')'
SELECT @params = N'@fromdate datetime, ' +
N'@todate datetime = NULL, ' +
N'@cnt int OUTPUT'
EXEC sp_executesql @sql, @params, '20060101', @cnt = @count OUTPUT

PRINT @tbl + ': ' + convert(varchar(10), @count) + ' modified rows.'
END

Finding The Total Numbe rows for all tables in a database using Sql server Query

EXEC sp_MSforeachtable @command1="EXEC sp_spaceused '?'"

Splitting string in sql server

Splitting a String using Sql Server query in Sql Server


declare @OrderList as varchar(500)
set @OrderList='235,Ravi,SNY,Advanced Development,0.3,0.7,0.5,0.5,0.5,0.5,0.5,0.5,0.5,0.5,0.5,0.5,02/17/2010;
194,Chris Edwards,SNY,CAD,0,0,0,0,0,0,0,0,0,0,0,,11/27/2009;
41,Henry Nguyen,SNY,Mechanical,0,0,0,0,0,0,0,0,0,0,0,,11/25/2009;
97,Baber Raza,SNY,Product Engineering,0,0,0.1,0,0,0,0,0,0,0,0,,11/25/2009;
100,Steve Rojas,SNY,Product Engineering,0,0,0,0,0,0,0,0,0,0,0,,11/29/2009;
101,Haijiang Zhang,SNY,Product Engineering,0,0,0,0,0,0,0,0,0,0,0,,11/26/2009;
116,Benny Tan,SGP,Product Engineering,0,0,0,0,0,0,0,0,0,0,0,,11/26/2009;
117,Jen Kwang Han,SGP,Product Engineering,0,0,0,0,0,0,0,0,0,0,0,,11/25/2009;
59,Shanker Maduri,SNY,Program Manager,0,0,0,0,0,0,0,0,0,0,0,,11/26/2009
;'
SET NOCOUNT ON

DECLARE @TempList table
(
OrderID varchar(30)
)

DECLARE @OrderID varchar(10), @Pos int

SET @OrderList = LTRIM(RTRIM(@OrderList))+ ','
SET @Pos = CHARINDEX(',', @OrderList, 1)

IF REPLACE(@OrderList, ';', '') <> ''
BEGIN
WHILE @Pos > 0
BEGIN
SET @OrderID = LTRIM(RTRIM(LEFT(@OrderList, @Pos - 1)))
IF @OrderID <> ''
BEGIN
INSERT INTO @TempList (OrderID) VALUES (CAST(@OrderID AS varchar(30))) --Use Appropriate conversion
END
SET @OrderList = RIGHT(@OrderList, LEN(@OrderList) - @Pos)
SET @Pos = CHARINDEX(',', @OrderList, 1)

END
END

select * from @TempList

UpdateStatistics of Sql server

CREATE PROC usp_UPDATE_STATISTICS
(@dbName sysname, @sample int)
AS

SET NOCOUNT ON

DECLARE @SQL nvarchar(4000)
DECLARE @ID int
DECLARE @TableName sysname
DECLARE @RowCnt int

CREATE TABLE ##Tables
(
TableID INT IDENTITY(1, 1) NOT NULL,
TableName SYSNAME NOT NULL
)

SET @SQL = ''
SET @SQL = @SQL + 'INSERT INTO ##Tables (TableName) '
SET @SQL = @SQL + 'SELECT [name] '
SET @SQL = @SQL + 'FROM ' + @dbName + '.dbo.sysobjects '
SET @SQL = @SQL + 'WHERE xtype = ''U'' AND [name] <> ''dtproperties'''

EXEC sp_executesql @statement = @SQL

SELECT TOP 1 @ID = TableID, @TableName = TableName
FROM ##Tables
ORDER BY TableID

SET @RowCnt = @@ROWCOUNT

WHILE @RowCnt <> 0
BEGIN

SET @SQL = 'UPDATE STATISTICS ' + @dbname + '.dbo.[' + @TableName + '] WITH SAMPLE ' + CONVERT(varchar(3), @sample) + ' PERCENT'

EXEC sp_executesql @statement = @SQL

SELECT TOP 1 @ID = TableID, @TableName = TableName
FROM ##Tables
WHERE TableID > @ID
ORDER BY TableID

SET @RowCnt = @@ROWCOUNT

END

DROP TABLE ##Tables


GO

Split function in sql server 2000 or sql server 2005

declare @value nvarchar(50)
declare @splitter nvarchar(50)
declare @delimit nvarchar(1)
select
@delimit = '/'
select
@splitter = 'Any Section/Department A'
SET
@value = SUBSTRING(@splitter,1,CHARINDEX(@delimit,@splitter)-1)
--For Previous string to the delimeter
select
@value as retSplittedValue
--For Next string to the delimeter
select substring(@splitter,(len(@value)+2),(len(@splitter))-len(@value))

using case and if in sql server query

SELECT '(X' + x.paramno + ')' + x.xParameterName AS Xparametername, '(Y' + y.paramno + ') ' + y.yParameterName AS Yparametername,
'UnitNo' + u.paramno + ' (' + u.uParameterName + ')' AS Unitparametername
FROM (SELECT CASE substring(parametername, 1, 1) WHEN 'X' THEN substring(parametername, 2, 100) WHEN 'Y' THEN substring(parametername, 2, 100)
WHEN ('U') THEN substring(parametername, 7, 100) ELSE NULL END AS paramno, SUBSTRING(ParameterName, 1, 1) AS indicator,
CAST(ParamValueInt AS varchar(10)) AS xParameterName
FROM insite.dc_Eval_CoordinateInput
WHERE (SUBSTRING(ParameterName, 1, 1) = 'X')) AS x INNER JOIN
(SELECT CASE substring(parametername, 1, 1) WHEN 'X' THEN substring(parametername, 2, 100) WHEN 'Y' THEN substring(parametername, 2,
100) WHEN ('U') THEN substring(parametername, 7, 100) ELSE NULL END AS paramno, SUBSTRING(ParameterName, 1, 1) AS indicator,
CAST(ParamValueInt AS varchar(10)) AS yParameterName
FROM insite.dc_Eval_CoordinateInput
WHERE (SUBSTRING(ParameterName, 1, 1) = 'Y')) AS y ON x.paramno = y.paramno INNER JOIN
(SELECT CASE substring(parametername, 1, 1) WHEN 'X' THEN substring(parametername, 2, 100) WHEN 'Y' THEN substring(parametername, 2,
100) WHEN ('U') THEN substring(parametername, 7, 100) ELSE NULL END AS paramno, SUBSTRING(ParameterName, 1, 1) AS indicator,
CAST(ParamValueInt AS varchar(10)) AS uParameterName
FROM insite.dc_Eval_CoordinateInput
WHERE (SUBSTRING(ParameterName, 1, 1) = 'U')) AS u ON x.paramno = u.paramno

How to change the Author of table using a t-sql

How to change the Author of table using a t-sql


sp_changeobjectowner @objname = 'username.tablename' , @newowner = 'dbo'