August 7, 2013 at 7:48 am
Hi,
I have a scenario in which I have to find a column with varchar or nvarchar datatype in all tables of a database and update the value of those columns with special characters like '!@#$%^&*()'.
Here is the DDL script :
CREATE TABLE table1
(
ID INT IDENTITY,
Name1 VARCHAR(50)
)
GO
CREATE TABLE table2
(
ID INT IDENTITY,
Name2 VARCHAR(50)
)
GO
CREATE TABLE table3
(
ID INT IDENTITY,
Name3 VARCHAR(50)
)
INSERT INTO table1
SELECT 'a'
UNION ALL
SELECT 'b'
GO
INSERT INTO table2
SELECT 'c'
UNION ALL
SELECT 'd'
GO
INSERT INTO TABLE3
SELECT 'e'
UNION ALL
SELECT 'f'
Expected Output:
table1 '!@#$%^&*()a'
table1 '!@#$%^&*()b'
table2 '!@#$%^&*()c'
table2 '!@#$%^&*()d'
table3 '!@#$%^&*()e'
table3 '!@#$%^&*()f'
I just have to add these special characters in front of columnvalues whose datatype is varchar or nvarchar..
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
August 7, 2013 at 7:59 am
like this?
update table1 set Name1 = '!@#$%^&*()' + Name1
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 7, 2013 at 8:19 am
Sean Lange (8/7/2013)
like this?
update table1 set Name1 = '!@#$%^&*()' + Name1
In this manner, but only for those tables who has column of varchar types.....
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
August 7, 2013 at 8:28 am
kapil_kk (8/7/2013)
Sean Lange (8/7/2013)
like this?
update table1 set Name1 = '!@#$%^&*()' + Name1
In this manner, but only for those tables who has column of varchar types.....
Are you trying to add that to all columns in all tables with a datatype of varchar?
Is this something you need to be dynamic? It seems very strange to update every row of every column in every table like this.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 7, 2013 at 8:29 am
And even worse...what are going to do when the column is not wide enough to hold the current value plus this new string?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 7, 2013 at 8:49 am
Sean Lange (8/7/2013)
kapil_kk (8/7/2013)
Sean Lange (8/7/2013)
like this?
update table1 set Name1 = '!@#$%^&*()' + Name1
In this manner, but only for those tables who has column of varchar types.....
Are you trying to add that to all columns in all tables with a datatype of varchar?
Is this something you need to be dynamic? It seems very strange to update every row of every column in every table like this.
Yes I want to do it for all columns in all tables with datatype varchar
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
August 7, 2013 at 8:54 am
Sean Lange (8/7/2013)
And even worse...what are going to do when the column is not wide enough to hold the current value plus this new string?
In this case I will change the size of the column.....
Well mostly all the tables has varchar(100) and these are of sufficient size for the values tht is stored
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
August 7, 2013 at 10:22 am
This is still the strangest requirement I have heard of in years...I can't come up with any rational reason why you would want to blindly modify all the data in every single column and add gibberish to it. Can you enlighten on how this could be useful?
This will generate the update statements for you using the system catalogs.
select 'update ' + so.name + ' set ' + sc.name + ' = ''!@#$%^&*()'' + ' + sc.name + ';'
from sys.objects so
join sys.columns sc on sc.object_id = so.object_id
join sys.types st on st.system_type_id = sc.system_type_id
where st.name = 'varchar'
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply