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

Advertisements

SQL Server – Count table rows for all databases using sp_MSforeachdb and sp_MSforeachtable

This post is inspired from one of other post in SQL Server Geeks by Ahmad Osama.

http://www.sqlservergeeks.com/sql-server-sp_spaceused-returns-wrong-count/

I would like to extend some of my ideas further to get the table count as below. At times, SQL Server developers/DBAs might need to know the table row count for all tables from all databases available on a server. There are various approaches to get the row counts in SQL Server.

Here are few approaches as below:
Approach 1:


DECLARE @TableRowCounts TABLE ([databaseNAme] Varchar(100),[TableName] VARCHAR(128), [RowCount] INT) ;
	INSERT INTO @TableRowCounts ([databaseNAme],[TableName], [RowCount])
	EXEC sp_MSforeachdb 'select ''?'' as database_name,o.name,max(i.rowcnt )
						  From sys.objects o 
						inner join sys.sysindexes i on o.object_id=i.id 
						where o.type=''U'' 
						group by o.name' ;
						
Select * From @TableRowCounts

For Partition tables, the above query can be changed a bit as below:
Transact-SQL


DECLARE @TableRowCounts TABLE ([databaseNAme] Varchar(100),[TableName] VARCHAR(128), [RowCount] INT) ;
	INSERT INTO @TableRowCounts ([databaseNAme],[TableName], [RowCount])
	EXEC sp_MSforeachdb 'SELECT ''?'',TBL.name, SUM(PART.rows) AS rows
						FROM sys.tables TBL
						INNER JOIN sys.partitions PART ON TBL.object_id = PART.object_id
						INNER JOIN sys.indexes IDX ON PART.object_id = IDX.object_id
						AND PART.index_id = IDX.index_id
						WHERE IDX.index_id < 2
						GROUP BY TBL.object_id, TBL.name;' ;
						
Select * From @TableRowCounts

Approach 2:


DECLARE @TableRowCounts TABLE ([databaseNAme] Varchar(100),[TableName] VARCHAR(128), [RowCount] INT) ;
	INSERT INTO @TableRowCounts ([databaseNAme],[TableName], [RowCount])
	
EXEC sp_MSforeachdb 'SELECT ''?'',OBJECT_NAME(object_id), SUM(row_count) AS rows
FROM sys.dm_db_partition_stats
WHERE index_id < 2
GROUP BY OBJECT_NAME(object_id)' ;

Select * From @TableRowCounts

Likewise, there are still more ways to get the info in SQL Server. I do not really want to list all the methods, as a simple google search would end up with a lots of scripts for the same. Rather, want to deal about one of major drawbacks with such approaches is it may or may not be accurate in value. If you look at the MSDN articles for each catalogs used in the above approaches, you can see that it clearly suggests based on the approximate values. So if we are looking for an accurate value for tables that are undergoing frequent DELETE/INSERT, then we should really rely on COUNT() function. As COUNT function reads the data, the row count would be more accurate than any other methods.

Here is my attempt to get the count using sp_MSforeachdb and sp_MSforeachtable. I tried to make the script as simple as possible. You may have a look at below:


create Table  TableRowCounts  ([databaseNAme] Varchar(100),[TableName] VARCHAR(128), [RowCount] INT) ;
 
EXEC sp_MSforeachdb
@command1 = 'IF not exists(select 1 where ''?'' in (''master'',''model'',''msdb'',''tempdb''))
				INSERT INTO TableRowCounts ([databaseNAme],[TableName], [RowCount])
				EXEC [?].dbo.sp_MSforeachtable 
				@command1 = ''SELECT ''''?'''',''''&'''',count(1) FROM &'',
					@replacechar = ''&'''

Select * From TableRowCounts
					
Drop Table TableRowCounts
 

Note: You should be very careful as its a very expensive query, you may use at your own risk.Both sp_MSforEachDB and sp_MSforEachtable are undocumented as well.

Hope you enjoyed this post, please share your thoughts on the same.

How to identify Primary and foreign key column information in SQL Server

Here is a small script to get information about the Primary Key column(s) and Foreign Key column(s) in SQL Server.

Script:


;WITH GETINFO AS
(
	SELECT	DBNAME = CONVERT(SYSNAME,DB_NAME()), 
				OWNERNAME = CONVERT(SYSNAME,SCHEMA_NAME(OBJ1.SCHEMA_ID)), 
				[PRIMARYTABLE NAME] = CONVERT(SYSNAME,OBJ1.NAME), 
				PKCOLUMN_NAME = CONVERT(SYSNAME,COL1.NAME), 
				[FOREIGNTABLE NAME]= CONVERT(SYSNAME,OBJ2.NAME), 
				FKCOLUMN_NAME = CONVERT(SYSNAME,COL2.NAME), 
				ORDINALPOSITION             = ISNULL(CONVERT(SMALLINT,FKCOL.CONSTRAINT_COLUMN_ID),0),
				[FOREIGNKEY NAME] = CONVERT(SYSNAME,OBJECT_NAME(FK.OBJECT_ID)), 
				[PRIMARYKEY NAME] = CONVERT(SYSNAME,I.NAME)
	FROM   SYS.ALL_OBJECTS OBJ1, SYS.ALL_OBJECTS OBJ2, SYS.ALL_COLUMNS COL1, SYS.ALL_COLUMNS COL2, SYS.FOREIGN_KEYS FK 
	INNER JOIN SYS.FOREIGN_KEY_COLUMNS FKCOL ON (FKCOL.CONSTRAINT_OBJECT_ID = FK.OBJECT_ID) 
	INNER JOIN SYS.INDEXES I  ON (FK.REFERENCED_OBJECT_ID = I.OBJECT_ID AND FK.KEY_INDEX_ID = I.INDEX_ID) 
	WHERE  OBJ1.OBJECT_ID = FK.REFERENCED_OBJECT_ID AND OBJ2.OBJECT_ID = FK.PARENT_OBJECT_ID 
				AND COL1.OBJECT_ID = FK.REFERENCED_OBJECT_ID AND COL2.OBJECT_ID = FK.PARENT_OBJECT_ID 
				AND COL1.COLUMN_ID = FKCOL.REFERENCED_COLUMN_ID AND COL2.COLUMN_ID = FKCOL.PARENT_COLUMN_ID
),SHOWINFO AS
(
	SELECT DISTINCT DBNAME,OWNERNAME,[PRIMARYTABLE NAME],[FOREIGNTABLE NAME],
           PRIMARYKEYCOLUMNNAMES=  REPLACE( 
			   (SELECT A.PKCOLUMN_NAME AS [data()]
				FROM GETINFO A
				WHERE A.[PRIMARYTABLE NAME] = B.[PRIMARYTABLE NAME] AND A.[FOREIGNKEY NAME] = B.[FOREIGNKEY NAME] AND A.[PRIMARYKEY NAME] = B.[PRIMARYKEY NAME]
				ORDER BY A.ORDINALPOSITION FOR XML PATH ('') ), ' ', ',') ,
           FOREIGNKEYCOLUMNNAMES=  REPLACE( 
			   (SELECT A.FKCOLUMN_NAME AS [data()]
                FROM GETINFO A
                WHERE A.[PRIMARYTABLE NAME] = B.[PRIMARYTABLE NAME] AND A.[FOREIGNKEY NAME] = B.[FOREIGNKEY NAME] AND A.[PRIMARYKEY NAME] = B.[PRIMARYKEY NAME]
                ORDER BY A.ORDINALPOSITION FOR XML PATH ('') ), ' ', ',') 
	FROM GETINFO B 
)SELECT * FROM SHOWINFO 

T-SQL Script to restore backup files from a folder in SQL Server

Very often, myself or my friends were in need of a script to restore the backups from a predefined folder. It was hard to write the script in the hour of need. Hence, I thought I will share the script that has been created recently. Hope this script would be useful for you as well.


DECLARE @BACKUPFILENAME VARCHAR(MAX) = ''
DECLARE @MDFLOGICALNAME NVARCHAR(100)
DECLARE @LDFLOGICALNAME NVARCHAR(100)
DECLARE @DEST_PATH NVARCHAR(100)
DECLARE @SQL NVARCHAR(MAX)
DECLARE @DATABASENAME SYSNAME
CREATE TABLE #TRAILUPGRADEBACKUPFILES (ID INT IDENTITY(1,1),FILENAME SYSNAME,RESTORE_DRIVE CHAR(1))

/*********************************************************************************************************************************/
--The below code fetches the file list(backup file names) to be restored from the pre-defined folder
DECLARE @CommandShell TABLE ( Line VARCHAR(512)) 
DECLARE  @CMD VARCHAR(512) ,@BackupFilePath NVARCHAR(256)
SET @BackupFilePath = 'C:\TEMP_TOBEDELETED\' 
SET @CMD = 'DIR /B ' + @BackupFilePath +  ' /TC' 
				
INSERT INTO @CommandShell EXEC MASTER..xp_cmdshell   @CMD 
    
-- Delete lines not containing filename
DELETE 
FROM   @CommandShell 
WHERE  Line is null

If((Select Top 1 Line From @CommandShell) = 'Access is denied.') 
    PRINT 'Folder access is not provided for network path'
				
INSERT INTO #TRAILUPGRADEBACKUPFILES (FILENAME)
	Select * From @CommandShell A WHERE CHARINDEX('.BAK',LINE) > 0
/*********************************************************************************************************************************/

CREATE TABLE #TFILELISTONLY 
(
    TLOGINNAME SYSNAME,TPHYSICALNAME VARCHAR(MAX),
    TTYPE VARCHAR(1),TFILEGROUPNAME  VARCHAR(MAX),
    TSIZE BIGINT,TMAXSIZE BIGINT,TFIELD  VARCHAR(MAX),
    TCREATELSN VARCHAR(MAX),TDROPLSN VARCHAR(MAX),
    TUNIQUEID VARCHAR(MAX),READONLYLSN VARCHAR(MAX),
    READWRITELSN VARCHAR(MAX),BACKUPSIZEINBYTES VARCHAR(MAX),
    SOURCEBLOCKSIZE VARCHAR(MAX),FILEGROUPID VARCHAR(MAX),
    LOGGROUPGUID VARCHAR(MAX),DIFFERENTIALLSN VARCHAR(MAX),
    DIFFERENTIALBASEGUID VARCHAR(MAX),ISREADONLY VARCHAR(MAX),
    ISPRESENT VARCHAR(MAX),TDEHUMBPRINT VARCHAR(MAX),
)

WHILE EXISTS(SELECT 1 FROM #TRAILUPGRADEBACKUPFILES)
BEGIN
	
	SET @BACKUPFILENAME = (SELECT TOP 1 FILENAME FROM #TRAILUPGRADEBACKUPFILES ORDER BY FILENAME ASC)
	--Restore Path needs to be provided in the below
	SET @DEST_PATH = 'C:\DATA\'
		
	SET @DATABASENAME = (SUBSTRING(@BACKUPFILENAME,0,CHARINDEX('.',@BACKUPFILENAME)))
				
	--To check if the database is already present in the environment, If present, Drop the database
	IF Exists(SELECT 1 FROM master.sys.databases WHERE name = @DATABASENAME)
	BEGIN
	
		EXEC('DROP Database '+@DATABASENAME)
		Print @BACKUPFILENAME + ' has been successfully dropped.'
		
	END

	INSERT INTO #TFILELISTONLY
		EXEC ('RESTORE FILELISTONLY FROM DISK ='''+ @BackupFilePath +  @BACKUPFILENAME+'''')

	SET @MDFLOGICALNAME = (SELECT TLOGINNAME FROM #TFILELISTONLY WHERE TTYPE ='D')
	SELECT @LDFLOGICALNAME = (SELECT TLOGINNAME FROM #TFILELISTONLY WHERE TTYPE ='L')
        
	SELECT @SQL ='RESTORE DATABASE ' + @DATABASENAME + ' FROM DISK = ''' +  @BackupFilePath + @BACKUPFILENAME + ''' 
		WITH MOVE ''' + @MDFLOGICALNAME + ''' TO ''' + @DEST_PATH + @DATABASENAME + '.MDF'', 
		MOVE '''  + +  @LDFLOGICALNAME + ''' TO ''' + @DEST_PATH + @DATABASENAME + '.LDF'''
	
	EXEC (@SQL)
	
	Print @BACKUPFILENAME + ' has been successfully restored.'
		
	EXEC ('ALTER DATABASE [' + @DATABASENAME + '] SET RECOVERY SIMPLE;')

	DELETE FROM #TFILELISTONLY


	DELETE FROM #TRAILUPGRADEBACKUPFILES WHERE FILENAME =@BACKUPFILENAME	

END

DROP TABLE #TFILELISTONLY,#TRAILUPGRADEBACKUPFILES

How to identify start up Parameters in SQL Server using T-SQL

Here is a very simple script to identify the startup parameter in SQL Server using T-SQL.


SELECT
    registry_key,
    value_name,
    value_data
FROM sys.dm_server_registry 
WHERE 
    registry_key LIKE N'%MSSQLServer\Parameters';

If you are aware of any other methods, I request you to share for other’s benefits.

EDIT: (On 22nd Aug 2016)
One of my colleague executed the above script and reported me back that he is getting the below error:

Msg 208, Level 16, State 1, Line 9
Invalid object name 'sys.dm_server_registry'.

The issue was that the version of his SQL Server was SQL Server 2008R2 (RTM).
This is a new DMV introduced from service pack (SP1) and available only for later versions of SQL Server 2008R2(SP1).

How to list file names in a folder in SQL Server

Here is a very small script to list the file names in a folder using SQL Server.

The below snippet has been useful for us to automate the restore process from a defined path(for large number of backup files).


/**********************************************************************/
	DECLARE @COMMANDSHELL TABLE ( LINE VARCHAR(512)) 
	DECLARE  @CMD VARCHAR(512) ,@BACKUPFILEPATH NVARCHAR(256)
	SET @BACKUPFILEPATH = 'D:\DATABASES\BACKUPS\' 
	SET @CMD = 'DIR /B ' + @BACKUPFILEPATH +  ' /TC' 
				
	INSERT INTO @COMMANDSHELL EXEC MASTER..XP_CMDSHELL   @CMD 

	--Data Clean up 
	DELETE FROM   @COMMANDSHELL WHERE  LINE IS NULL

	SELECT* FROM @COMMANDSHELL A
/**********************************************************************/

Prerequisites: You may need to enable the xp_cmdshell using sp_configure as below:


--To Enable the advanced Options
EXEC SP_CONFIGURE 'SHOW ADVANCED OPTIONS',1
RECONFIGURE WITH OVERRIDE

--To Enable XP_CmdShell 
EXEC SP_CONFIGURE 'XP_CMDSHELL',1
RECONFIGURE WITH OVERRIDE

--To Disable the advanced Options(by default)
EXEC SP_CONFIGURE 'SHOW ADVANCED OPTIONS',0
RECONFIGURE WITH OVERRIDE