| Author |
 Topic  |
|
|
jayram
Starting Member
27 Posts |
 Posted - 09/24/2012 : 16:32:17
|
Hello,
I have around 50 tables in a database where a few of them have a column called NAME and i want to remove apostrophe from the NAME column in all these tables
i can do it individually by replace(NAME,'''','')
but is there Dynamic SQL method to look up all Table thah have NAME column and generate an Update statement.
in the past i have used this in order to round variables in a table
SELECT 'UPDATE TABLE SET '
UNION ALL
SELECT quotename(column_name) + '=ROUND(convert(numeric(18,4),' + quotename(column_name) + '), 3), '
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME='DRUG' AND DATA_TYPE='FLOAT'
Is there something similar for apostrophes?
Thanks
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47189 Posts |
Posted - 09/24/2012 : 17:01:21
|
yep.. use something like
EXEC sp_Msforeachtable 'IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME = ''Name'') SELECT ''UPDATE ? SET NAME=REPLACE(NAME,'''''''''''''''','''''''')'''
copy and paste it otherwise you will mess with number of quotes
this will form query string for you
choose results as text run above query and copy and paste the result obtained onto a new window to execute the generated query
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
Edited by - visakh16 on 09/24/2012 17:06:12 |
 |
|
|
jayram
Starting Member
27 Posts |
Posted - 09/25/2012 : 09:05:28
|
Thanks Visakh
I did run it but it pulls out an update script for all the tables not just the tables that have a NAME column. |
|
|
|
webfred
Flowing Fount of Yak Knowledge
Germany
8515 Posts |
Posted - 09/25/2012 : 09:26:41
|
quote:
Originally posted by jayram
Thanks Visakh
I did run it but it pulls out an update script for all the tables not just the tables that have a NAME column.
But it will do the update only if there is a column named Name.
See the IF EXISTS...
Too old to Rock'n'Roll too young to die. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47189 Posts |
Posted - 09/25/2012 : 12:54:59
|
quote:
Originally posted by jayram
Thanks Visakh
I did run it but it pulls out an update script for all the tables not just the tables that have a NAME column.
not true
then that means field name is not exactly Name but it may have some other parts as well
if thats case change logic from = to LIKE using %
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
jayram
Starting Member
27 Posts |
Posted - 09/26/2012 : 15:32:41
|
i do this
EXEC sp_Msforeachtable
'IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME like ''%Name%''
and (TABLE_NAME like ''%pr%'' or TABLE_NAME like ''%HO%''))
SELECT ''UPDATE ? SET NAME=REPLACE(NAME,'''''''''''''''','''''''')'''
but it still retrieves an update statement for tables without name like pr or ho
|
|
|
|
webfred
Flowing Fount of Yak Knowledge
Germany
8515 Posts |
Posted - 09/26/2012 : 17:05:35
|
It is because the IF EXISTS() has no relation to the actual table in progress by sp_Msforeachtable...
maybe this:
EXEC sp_Msforeachtable 'IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME = ''Name'' and table_name = parsename(''?'',1)) SELECT ''UPDATE ? SET NAME=REPLACE(NAME,'''''''''''''''','''''''')'''
Too old to Rock'n'Roll too young to die.
edit: inserted parsename to get the plain table name... |
Edited by - webfred on 09/27/2012 03:08:27 |
|
|
| |
Topic |
|
Removing apostrophe from multiple tables
