The STUFF function inserts a string into another string. It deletes a specified length of characters in the first string at the start position and then inserts the second string into the first string at the start position.
STUFF ( character_expression , start , length , replaceWith_expression )
Is an expression of character data. character_expression can be a constant, variable, or column of either character or binary data.
Is an integer value that specifies the location to start deletion and insertion. If start or length is negative, a null string is returned. If start is longer than the first character_expression, a null string is returned. start can be of type bigint.
Is an integer that specifies the number of characters to delete. If length is longer than the first character_expression, deletion occurs up to the last character in the last character_expression. length can be of type bigint.
Is an expression of character data. character_expression can be a constant, variable, or column of either character or binary data. This expression will replace length characters of character_expression beginning at start.
SELECT STUFF(‘abcdef’, 2, 3, ‘ijklmn’)
There is a function named RIGHT in MS SQL Server which returns the right part of a character string with the specified number of characters.
RIGHT ( string , No_of_Character )
SELECT RIGHT(‘The quick brown fox jumps over a little lazy dog.’, 23)
over a little lazy dog.
There is a function named LEFT in MS SQL Server which returns the left part of a character string with the specified number of characters.
LEFT ( string , No_of_Character )
SELECT LEFT(‘The quick brown fox jumps over a little lazy dog.’, 19)
The quick brown fox
If we want to perform an action on each and every tables (as it is self explanatory “for each Table” ) in a database.
Common uses of this store procedure.
- Truncate all the tables
- Disable check constraints for all tables
- Run delete command on all the tables
- To get the size of all the tables in the database
- To rebuild all indexes of all the tables in the database
- Disable all constraints and triggers of all the tables in the database
- Delete all the data from all the tables in the database
- To RESEED all tables to 0
- To get the Number of Rows in all tables in a database
- Update the statistics of all the tables in a database
- Reclaim space from dropped variable-length columns in tables or indexed views of the database
MS Sql Server provides you with some undocumented stored procedure “sys.sp_MSforeachTable”. It allows you to process some code against each and every table in a single database. This is a system stored procedure which comes with SQL server by default. This function has ability to run single command or multiple commands against tables in a single database.
sp_MSforeachtable [ @command1 = ] ‘command1’ [ , [ @replacechar = ] replacechar ] [ , [ @command2 = ] command2 ] [ , [ @command3 = ] command3 ] [ , [ @whereand = ] where_and_Condition ] [ , [ @precommand = ] precommand] [ , [ @postcommand = ] postcommand]
||It is the first command to be executed by this Stored Procedure and the data type is nvarchar(2000).
||It is a character in the command string that needs to be replaced with the table name being processed. The default value of this parameter is a “?”.
||@command2 and @command3 are two additional commands that can be run for each table. Here first Command1 is executing then command2 and then command3 will execute.
||This parameter could be used to provide additional constraints to the command for helping to identify the rows in the sysobjects table that will be selected. Its data type is nvarchar(2000).
||This command is to be run before processing any table. Its data type is nvarchar(2000).
||This command is to be run after the processing of all the tables. Its data type is nvarchar(2000).
sp_MSforeachtable can be used to loop through all the tables in your databases. Here are some common usages of this useful stored procedure:
— Display the size of all tables in a database
EXEC sp_MSforeachtable @command1=”EXEC sp_spaceused ‘?'”
— Display Number of Rows in all Tables in a database
EXEC sp_MSforeachtable ‘SELECT ”?”, Count(*) as NumberOfRows FROM ?’
— Diable trigger on all the tables
EXEC sp_MSforeachtable ‘ALTER TABLE ? DISABLE TRIGGER ALL’
— Disable referential integrity
EXEC sp_MSForEachTable ‘ALTER TABLE ? NOCHECK CONSTRAINT ALL’
— Enable referential integrity again
EXEC sp_MSForEachTable ‘ALTER TABLE ? CHECK CONSTRAINT ALL’
— To RESEED all table to 0, use this script
EXEC sp_MSForEachTable ‘
IF OBJECTPROPERTY(object_id(”?”), ”TableHasIdentity”) = 1
DBCC CHECKIDENT (”?”, RESEED, 0)
— Update Statistics of all Tables in a database
EXEC sp_MSforeachtable ‘UPDATE statistics ? WITH ALL’
One of my colleague ask me a query to retrieve all the database names in a SQL Server.
Here is the SQL Server Query to retrieve the same.