Tag: SQL Server

Indexes, SQL

Extending sp_helpindex to get more useful information about index in SQL Server

This post is to help people who had tough time to get information about index and its included columns in SQL Server. Whenever I need, I always depend on object explorer and get the definition of the index.So I thought of compiling a snippet as below that can provide these information handy.

I created a procedure called – sp_helpindexExtended as below and the Tablename as parameter(this is an optional parameter).

Procedure Definition


Create Proc sp_helpindexExtended (@TableName sysname = NULL) 
as 
Begin 
	  ;With cte as 
	  (
		 Select Object_name(A.object_id) objectname,A.name index_name,
			Lower(type_desc) + Case When IS_Unique =1 then ', unique' Else '' end +
			Case when is_primary_key =1 Then ', primary key' Else '' End+
			Case when IS_Unique_Constraint = 1 Then ',unique key' Else '' End  index_description,
			Case when is_included_column=0 then c.name  + '('+
            Case When System_type_id = User_Type_id Then Type_Name(System_type_id) Else Type_Name(User_Type_id) End +')'  Else NULL end ColumnName,
            Case when is_included_column=1 then c.name  + '('+
            Case When System_type_id = User_Type_id Then Type_Name(System_type_id) Else Type_Name(User_Type_id) End +')'  Else NULL end IncludedColumnnames
            ,Is_Unique,Is_Primary_Key,IS_Unique_Constraint,Fill_factor,key_ordinal
    From sys.indexes A  
    Inner Join sys.index_columns B On A.object_id = B.object_id And A.index_id = B.index_id 
    Inner Join sys.columns C On c.object_id = B.object_id  And C.column_id  = B.column_id 
	Where A.Object_ID = Case when @TableName is not null Then OBJECT_ID(@TableName) Else A.object_id End
	)	SELECT objectname,index_name,index_description,
       stuff(( SELECT ','+ColumnName AS [text()] FROM cte p2
          WHERE p2.objectname = p1.objectname and p2.index_name = p1.index_name
          ORDER BY key_ordinal asc FOR XML PATH('') ), 1, 1,'') AS index_keys,
		stuff( ( SELECT IsNull(','+IncludedColumnnames ,'')  FROM cte p2
          WHERE p2.objectname = p1.objectname and p2.index_name = p1.index_name
          ORDER BY key_ordinal asc FOR XML PATH('') ), 1, 1,'')  AS included_keys
		  ,fill_factor 
      FROM cte p1 GROUP BY objectname,index_name ,index_description,fill_factor 
	 order by objectname asc
End

Procedure Usage

-- To get for all tables
exec sp_helpindexextended
-- To get only for a table
exec sp_helpindexextended 'tablename'
SCRIPT GALLERY, SQL

How to extract ONLY numbers from a string in SQL Server

Here is a script to extract *ONLY* numbers from a string.


DECLARE @Sample TABLE(string VARCHAR(2000));
INSERT INTO @Sample
Values( 'hhjjj12345hhhkk'),('-7655'),('asd5-5dffgdfg105'),('hhjjj12345hhhkk'),('a1b2c3d4ee55fff666gggg7777h'),('Al1')
,('2Bob$'),('!Carl%'),('%D#?:"{}|\][+_)(*&^	%$#@!~`_+?>a*vi()(d&*(&*#?:"{}|\][J+o_n)es(*&^%$#@!~`_+?><,./;:"' ),('    Eddie     ')
;WITH Num1 (n) AS (SELECT 1 UNION ALL SELECT 1),
Num2 (n) AS (SELECT 1 FROM Num1 AS X, Num1 AS Y),
Num3 (n) AS (SELECT 1 FROM Num2 AS X, Num2 AS Y),
Num4 (n) AS (SELECT 1 FROM Num3 AS X, Num3 AS Y),
Nums (n) AS (SELECT ROW_NUMBER() OVER(ORDER BY n) FROM Num4),
FinalOutput AS (
SELECT string, (SELECT CASE WHEN SUBSTRING(string, n, 1) NOT LIKE '[^0-9]'
                       THEN SUBSTRING(string, n, 1)
                      ELSE ''
                  END + ''
           FROM Nums
           WHERE n <= LEN(string)
           FOR XML PATH('')) AS stringout
FROM @Sample)
SELECT string, stringout FROM FinalOutput;
SCRIPT GALLERY, SQL

How to replace first occurrence of a word in a sentence in SQL Server

Have you ever thought of How REPLACE is working? It just replaces all the occurrences of a word in the sentence.

Lets look at an example.


CREATE TABLE #StringTable ( id INT, string VARCHAR(128) )

INSERT #StringTable VALUES (1,'It was a pleasant morning, and he was the only kid in the garden')
,(1,'There is no match word')
Declare @searchstring varchar(100)='was',@Replacestring varchar(100)='is'

select id,string,
	Replace(string,@searchstring,@replacestring)
	from #StringTable
		cross apply (select (charindex(@searchstring, string))) as Search1(Pos)

Drop table #StringTable

In the above example, there are two occurrences of the word “was” for the first record. And the REPLACE function results in replacing all occurrences.

Suppose, if you want to only replace the first occurrence, How do you do?

First approach with CROSS APPLY and SUBSTRING:


CREATE TABLE #StringTable ( id INT, string VARCHAR(128) )

INSERT #StringTable VALUES (1,'It was a pleasant morning, and he was the only kid in the garden')
,(1,'There is no match word')

Declare @searchstring varchar(100)='was',@Replacestring varchar(100)='is'

select id,string,
	case when Search1.Pos != 0 then 
				substring(string, 0,Search1.Pos )+ @Replacestring +
				substring(string, Search1.Pos+LEN(@searchstring), LEN(string) )
	else string end ChangedText
	from #StringTable
		cross apply (select (charindex(@searchstring, string))) as Search1(Pos)

Drop table #StringTable

Another method with STUFF and PATINDEX:


CREATE TABLE #StringTable ( id INT, string VARCHAR(128) )

INSERT #StringTable VALUES (1,'It was a pleasant morning, and he was the only kid in the garden')
,(1,'There is no match word')

Declare @searchstring varchar(100)='was',@Replacestring varchar(100)='is'

Select id, string, Case when charindex(@searchstring, string) != 0 Then
				Stuff(string, patindex('%'+@searchstring+'%',string),len(@searchstring),@Replacestring) 
				Else string End ChangedText
	from #StringTable

Drop table #StringTable
Errors and Exceptions, SQL

ALTER DATABASE failed because a lock could not be placed on database ‘DBNAME’. Try again later. in SQL Server

At times, we may need to take a database offline. And there is a chance that we encounter an error message like below.
How do we solve this issue and take the database offline?


Msg 5061, Level 16, State 1, Line 1
ALTER DATABASE failed because a lock could not be placed on database 'DBNAME'. Try again later.

Solution:
If there are any connections open, then there is a lock on the database while getting the database into Single user mode/taking offline.
To resolve the issue, as a first step you need to kill all the sessions on the database followed by the ALTER database statement.


USE master;

DECLARE @killSessions varchar(8000) = '';  
SELECT @killSessions = @killSessions + 'kill ' + CONVERT(varchar(5), spid) + ';'  
FROM master..sysprocesses  
WHERE dbid = db_id('dbname')
EXEC(@killSessions); 

ALTER DATABASE dbname SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO
ALTER DATABASE dbname SET OFFLINE WITH ROLLBACK IMMEDIATE
GO
ALTER DATABASE dbname  SET MULTI_USER;
Errors and Exceptions, SQL

The number of row value expressions in the INSERT statement exceeds the maximum allowed number of 1000 row values.

One of my colleague was facing an issue while inserting 2000 values to a table variable as below:


 DECLARE @EMPLOYEEDETAILS TABLE
(OLDEMPLOYEECODE NVARCHAR(30), NEWEMPLOYEECODE NVARCHAR(30))

INSERT INTO @EMPLOYEEDETAILS(OLDEMPLOYEECODE, NEWEMPLOYEECODE)
VALUES
('93466','0000007'),
('93467','0000010'),
...
...(2000 Records)
...
('93467','00002000')

Error Message:
Msg 10738, Level 15, State 1, Line 1006
The number of row value expressions in the INSERT statement exceeds the maximum allowed number of 1000 row values.

This is a self explanatory error message as SQL Server does not support row value expressions for more than 1000 values.
To overcome the issue, the code has been modified as below and the query has been executed successfully.

Solution:


DECLARE @EMPLOYEEDETAILS TABLE
(OLDEMPLOYEECODE NVARCHAR(30), NEWEMPLOYEECODE NVARCHAR(30))

INSERT INTO @EMPLOYEEDETAILS(OLDEMPLOYEECODE, NEWEMPLOYEECODE)
SELECT * FROM (VALUES
('93466','0000007'),
('93467','0000010'),
...
...(2000 Records)
...
('93467','00002000')
) A(Col1, Col2)

You may share any other alternatives/thoughts….