Handling special characters with FOR XML PATH(”)

In sql server using stuff  the string replaced when its  have tag “<” or “>” then  &gt;  when its  “&” then &amp;

Example

select
stuff(
(select ‘, <‘ + name + ‘>’
from sys.databases
where database_id > 4
order by name
for xml path(”)
)
, 1, 2, ”) as namelist;

For avoid replace  &gt; or &amp;

 

select
stuff(
(select ‘, <‘ + name + ‘>’
from sys.databases
where database_id > 4
order by name
for xml path(”), root(‘MyString’), type
     ).value(‘/MyString[1]’,’varchar(max)’)
, 1, 2, ”) as namelist;

 

 

Advertisements

Last Day or First Day of given Previous or Current Month

 

Query

DECLARE @mydate DATETIME
SELECT @mydate = GETDATE()
SELECT CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(@mydate)),@mydate),101) ,
‘Last Day of Previous Month’
UNION
SELECT CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(@mydate)-1),@mydate),101) AS Date_Value,
‘First Day of Current Month’ AS Date_Type
UNION
SELECT CONVERT(VARCHAR(25),@mydate,101) AS Date_Value, ‘Today’ AS Date_Type
UNION
SELECT CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(DATEADD(mm,1,@mydate))),DATEADD(mm,1,@mydate)),101) ,
‘Last Day of Current Month’
UNION
SELECT CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(DATEADD(mm,1,@mydate))-1),DATEADD(mm,1,@mydate)),101) ,
‘First Day of Next Month’

Kill Session

When deadlock happen using this quires. i found the user and kill that session

Kill session

 –FIND KILLING SPID
SELECT
hostname,*
— net_library,
— net_address,
— client_net_address

FROM sys.sysprocesses AS S
INNER JOIN sys.dm_exec_connections AS decc ON S.spid = decc.session_id
WHERE S.open_tran=1
–WHERE spid = @@SPID –this query only check your session if hide its check all –session
–KILL 60

 

 

Identity column is not rollback

Example

when create table that time identity is 1

After Insert two records the identity is increase 2 then rollback the insert statement identity i snot rollback

Identity column is not rollback

USE TEST1
create table sample
(
id int identity(1,1),
name varchar(100)
)

select IDENT_CURRENT(‘sample’)
–Here Identity as 1

begin tran
insert into sample(name)
select ‘udhaya’ union
select ‘ganesh’
rollback

select IDENT_CURRENT(‘SAMPLE’)

–Here Identity as 2

Find UDT given spnames

 

Find UDT given spnames

SELECT
coalesce(object_schema_name(Referencing_ID)+’.’,”)+ –likely schema name
object_name(Referencing_ID)+ –definite entity name
coalesce(‘.’+col_name(referencing_ID,referencing_minor_id),”)
AS [referencing],
coalesce(Referenced_server_name+’.’,”)+ –possible server name if cross-server
coalesce(referenced_database_name+’.’,”)+ –possible database name if cross-database
coalesce(referenced_schema_name+’.’,”)+ –likely schema name
coalesce(referenced_entity_name,”) + –very likely entity name
coalesce(‘.’+col_name(referenced_ID,referenced_minor_id),”)AS [referenced],*
FROM sys.sql_expression_dependencies d
INNER JOIN sys.objects O ON d.referencing_id=O.object_id
WHERE O.name IN
(‘spname’)
and referenced_entity_name LIKE ‘UDT%’
ORDER BY [referenced]

Insert Script

Hi

This Script useful to get insert statement given table name

Insert Script

DECLARE @QUERY VARCHAR(MAX) = ‘Dbo.TableName WHERE 1=1′

SET NOCOUNT ON

DECLARE @WithStrINdex AS INT
DECLARE @WhereStrINdex AS INT
DECLARE @INDExtouse AS INT

DECLARE @SchemaAndTAble VARCHAR(270)
DECLARE @Schema_name VARCHAR(30)
DECLARE @Table_name VARCHAR(240)
DECLARE @Condition VARCHAR(MAX)

SELECT @WithStrINdex = 0
SELECT @WithStrINdex = CHARINDEX(‘WITH’, @Query), @WhereStrINdex = CHARINDEX(‘WHERE’, @Query)

IF(@WithStrINdex != 0)
SELECT @INDExtouse = @WithStrINdex
ELSE
SELECT @INDExtouse = @WhereStrINdex

SELECT @SchemaAndTAble = LEFT(@Query, @INDExtouse – 1)
SELECT @SchemaAndTAble = LTRIM(RTRIM(@SchemaAndTAble))

SELECT @Schema_name = LEFT(@SchemaAndTAble, CHARINDEX(‘.’, @SchemaAndTAble ) – 1)
,@Table_name = SUBSTRING(@SchemaAndTAble, CHARINDEX(‘.’, @SchemaAndTAble ) + 1, LEN(@SchemaAndTAble))
,@CONDITION = SUBSTRING(@Query, @WhereStrINdex + 6, LEN(@Query))–27+6
DECLARE @COLUMNS TABLE([Row_number] SMALLINT, Column_Name VARCHAR(MAX))
DECLARE @CONDITIONS AS VARCHAR(MAX)
DECLARE @Total_Rows AS SMALLINT
DECLARE @Counter AS SMALLINT

DECLARE @ComaCol AS VARCHAR(MAX)
SELECT @ComaCol = ”, @Counter = 1, @CONDITIONS = ”

–print @Schema_name
–print @Table_name

INSERT INTO @COLUMNS
SELECT ROW_NUMBER() OVER(ORDER BY ORDINAL_POSITION) [Count] ,COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = @Schema_name
AND TABLE_NAME = @Table_name
AND COLUMN_NAME NOT IN (‘SYNCDESTINATION’,’PENDINGSYNCDESTINATION’ ,’SKUID’,’SALECREDITEDTO’)

SELECT @Total_Rows = COUNT(*) FROM @COLUMNS
SELECT @Table_name = ‘[‘+@Table_name+’]’
SELECT @Schema_name = ‘[‘+@Schema_name+’]’

WHILE (@Counter< = @Total_Rows )
BEGIN
SELECT @ComaCol = @ComaCol + ‘ [‘+Column_Name+’],’ FROM @COLUMNS
Where [Row_number] = @Counter

SELECT @CONDITIONS = @CONDITIONS+ ‘ + CASE WHEN [‘+Column_Name+’] IS NULL THEN ”NULL” ELSE ”””” +
REPLACE( CONVERT(VARCHAR(MAX),[‘+Column_Name+’]) ,””””,””)
+”””” END +’+”’,”’
FROM @COLUMNS WHERE [Row_number] = @Counter

SET @Counter = @Counter + 1
END

SELECT @CONDITIONS = RIGHT(@CONDITIONS, LEN(@CONDITIONS) -2)

SELECT @CONDITIONS = LEFT(@CONDITIONS, LEN(@CONDITIONS) -4)

SELECT @ComaCol = SUBSTRING (@ComaCol, 0, LEN(@ComaCol))

SELECT @CONDITIONS = ”’INSERT INTO ‘ + @Schema_name + ‘.’ + @Table_name + ‘(‘ + @ComaCol + ‘)’ +’ VALUES( ‘+”” + ‘+’ + @CONDITIONS
SELECT @CONDITIONS = @CONDITIONS + ‘+’ + ”’)”’

SELECT @CONDITIONS = ‘SELECT’ + @CONDITIONS + ‘FROM’ + @Schema_name + ‘.’ + @Table_name + ‘ WITH(NOLOCK) ‘ + ‘ WHERE ‘ + @Condition
PRINT(@CONDITIONS)
EXEC(@CONDITIONS)