Change first character of table names with capital character including intermediate words using sp_rename

How about your collegue asking you to change all of your table names with capital character including intermediate words?

Here is a quick way for you to get it done.

Step 1: You need to create the below function. The below function would change the name that you pass to desired format. The below script is written by George Mastros (gmmastros), a mind-blowing script, may be the fastest for such scenarios.

Create FUNCTION dbo.InitialCap(@String VARCHAR(8000))
RETURNS VARCHAR(8000)
AS
	BEGIN 
	---------------------------------------------------------
	DECLARE @Position INT

	SELECT @String   = STUFF(LOWER(@String),1,1,UPPER(LEFT(@String,1))) COLLATE Latin1_General_Bin,
        @Position = PATINDEX('%[^A-Za-z''][a-z]%',@String COLLATE Latin1_General_Bin)

	WHILE @Position > 0
		SELECT @String   = STUFF(@String,@Position,2,UPPER(SUBSTRING(@String,@Position,2))) COLLATE Latin1_General_Bin,
				@Position = PATINDEX('%[^A-Za-z''][a-z]%',@String COLLATE Latin1_General_Bin)

	---------------------------------------------------------
	RETURN @String;
END 

Step 2: Verify the changes done by the above function. The below script will generate script to rename the tables using sp_rename . sp_rename is a system procedure to change the name of user created objects like tables, index, column or CLR user types. Do remember,when you change the object name, there is a chance of breaking your codes, so be careful!!!

select 'exec sp_rename @objname=[' + name + '], @newname=[' + dbo.InitialCap(name) + ']'
from sysObjects
where type = 'U'

Step 3: Apply the changes once verified.

Declare @s nvarchar(MAX)=''
select @s = @s+'exec sp_rename @objname=[' + name + '], @newname=[' + dbo.InitialCap(name)  +'];' 
from sysObjects
where type = 'U'
--print @s
exec(@s)
Advertisements

One thought on “Change first character of table names with capital character including intermediate words using sp_rename

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s