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

 

 

Find sp given keyword

In my project i have create more sps. sometime i forgot spname that time i’m using this query to search sp.

Find Sp Given Keyword

–Find SP
SELECT DISTINCT
o.name AS Object_Name,
o.type_desc
FROM sys.sql_modules m
INNER JOIN
sys.objects o
ON m.object_id = o.object_id
WHERE m.definition Like ‘%STUFF%’;

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)

How to create Data Dictionary Existing Tables

Download this DataDictionary qry

Query

;WITH DD (ID,RN,TBL,COL,DT,KEYY,NN)
AS
(
SELECT ID,
ROW_NUMBER() OVER (ORDER BY T.[Table]) AS RN,
T.[Table],T.[Column],T.[Data Type],T.[key],T.IsNullable
FROM
(
SELECT
T.object_id AS ID,
T.name as [Table],
C.column_id AS CID,
c.name AS [Column],
CASE WHEN I.DATA_TYPE LIKE ‘%VARCHAR%’ THEN I.DATA_TYPE +'(‘+CAST(I.CHARACTER_MAXIMUM_LENGTH AS varchar) +’)’
WHEN I.DATA_TYPE LIKE ‘%NUMERIC’ THEN I.DATA_TYPE +'(‘+CAST(I.NUMERIC_PRECISION AS varchar) +’,’+CAST(I.NUMERIC_SCALE AS varchar)+’)’
ELSE I.DATA_TYPE END as [Data Type],
CASE WHEN SUBSTRING(CONSTRAINT_NAME,1,2)=’PK’ THEN ‘PK’ ELSE ” END +”+
CASE WHEN FKT.NAME IS NOT NULL THEN ‘FK_’+FKT.name ELSE ” END
+’ ‘+ CASE WHEN DF.definition IS NOT NULL THEN ‘Default ‘+ DF.definition ELSE ” END as [key],
I.IS_NULLABLE as [IsNullable]FROM sys.tables AS T
INNER JOIN sys.columns C ON T.object_id=C.object_id
LEFT JOIN INFORMATION_SCHEMA.COLUMNS I ON I.TABLE_NAME=T.name AND I.COLUMN_NAME=C.NAME
LEFT JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU ON CU.TABLE_NAME=T.name AND CU.COLUMN_NAME=C.NAME
–INNER JOIN sys.objects OB ON OB.object_id=t.object_id
LEFT JOIN sys.foreign_key_columns FK on FK.parent_object_id =c.object_id and C.column_id=FK.parent_column_id
LEFT JOIN sys.foreign_keys Fky ON Fky.object_id=FK.constraint_object_id
LEFT JOIN sys.tables FKT ON FKT.object_id=Fky.referenced_object_id
LEFT JOIN sys.default_constraints DF ON DF.parent_object_id=C.object_id AND C.column_id=DF.parent_column_id) AS T
WHERE T.[Table] LIKE ‘TableName
GROUP BY T.ID,T.[Table],T.CID,T.[Column],T.[Data Type],T.[key],T.IsNullable
)
SELECT CASE WHEN T1.TBL=T2.TBL THEN ” ELSE T1.TBL END TBL,T1.COL,T1.DT,T1.KEYY,T1.NN FROM DD T1
LEFT JOIN DD T2 ON T2.RN=T1.RN -1
ORDER BY T1.ID,T1.RN

Output

TBL COL DT KEYY NN
Table Name col1 int PK NO
col2 int NO

Electricity Bill Calculation

–Run this Quiery
——ELECTRIC BILL
DECLARE @a as table(sno int,start int,finish int,rate numeric(5,2))
–INSERT INTO @a VALUES(1,0,100,50),(2,101,200,100),(3,201,300,150),(4,301,400,200),(5,401,500,250),(6,501,600,350)
INSERT INTO @a VALUES(1,1,100,0.5),(2,101,200,0.10),(3,201,300,0.15),(4,301,400,0.20),(5,401,500,0.25),(6,501,600,0.30),(7,601,700,0.35),(8,701,NULL,0.40)

–BILL AMOUNT
–DECLARE @b as table(bill int)

–INSERT @b VALUES(1000)
DECLARE @AMT INT =5000

—FIND A RATE

SELECT *,CASE WHEN T.Reading>=T.SumFinish AND T.Finish IS NOT NULL THEN T.Finish ELSE T.Reading-(T.SumFinish-(ISNULL(T.Finish,0))) END AS bal FROM
(
SELECT B.sno,MAX(B.Finish) Finish,MAX(A.start) as start ,SUM(A.finish) SumFinish,MAX(B.rate) rate,MAX(b.AMT) Reading FROM
(SELECT sno,start,finish,rate,@AMT AMT FROM @A) A
CROSS JOIN
(SELECT sno,start,finish,rate,@AMT AMT FROM @A) B
WHERE A.start<=B.start
GROUP BY B.SNO
)
T
where T.Reading>=T.start

———
SELECT T2.Reading,SUM(T2.rate*t2.bal) AMT FROM
(
SELECT *,CASE WHEN T.Reading>=T.SumFinish AND T.Finish IS NOT NULL THEN T.Finish ELSE T.Reading-(T.SumFinish-(ISNULL(T.Finish,0))) END AS bal FROM
(
SELECT B.sno,MAX(B.Finish) Finish,MAX(A.start) as start ,SUM(A.finish) SumFinish,MAX(B.rate) rate,MAX(b.AMT) Reading FROM
(SELECT sno,start,finish,rate,@AMT AMT FROM @A) A
CROSS JOIN
(SELECT sno,start,finish,rate,@AMT AMT FROM @A) B
WHERE A.start<=B.start
GROUP BY B.SNO
)
T
where T.Reading>=T.start
)
T2
WHERE T2.bal>0
GROUP by T2.Reading
–SELECT * FROM @a
SELECT * FROM @a

Split String

CREATEsplitstring FUNCTION [dbo].[SplitString] (
@myString nvarchar(max),
@deliminator nvarchar(10)
)
RETURNS @ReturnTable TABLE (
[id] [int] IDENTITY(1,1) NOT NULL,
[part] [nvarchar](max) NULL
)
AS BEGIN
Declare @iSpaces int
Declare @part nvarchar(max)
Set @myString = REPLACE(@myString,CHAR(39),CHAR(165))
Select @iSpaces = charindex(@deliminator,@myString,0)
While @iSpaces > 0
Begin
Select @part = substring(@myString,0,charindex(@deliminator,@myString,0))
Insert Into @ReturnTable(part)
Select REPLACE(@part,CHAR(165),CHAR(39))
Select @myString = substring(@mystring,charindex(@deliminator,@myString,0)+ len(@deliminator),len(@myString))
Select @iSpaces = charindex(@deliminator,@myString,0)
end
If len(@myString) > 0
Insert Into @ReturnTable
Select REPLACE(@myString,CHAR(165),CHAR(39))
RETURN
END

Run : select * from dbo.SplitString(‘mani-kandan’,’-‘)

Result :

id          part
1            mani
2            kandan