Help properly inserting quotes within an sql statement
-
It will be nice to automatically change table owners screwed up by a new apps
Intended sql
sp_changeobjectowner 'manager.table_name','dbo'
what is in place comes up with
sp_changeobjectowner manager .'table_name'.'dbo'
if you are interested in the sql that does this Look below for the short script
declare @table_name varchar(10)
declare @counter integer
declare @strname varchar(20)
declare @sqlscript varchar(2000)
declare @sqlscript1 varchar(2000)
--Drop table if it exists
drop table #temp --fails but that is OK
create table #temp( num int identity(1,1),table_name varchar(500))
insert into #temp
select table_name from INFORMATION_SCHEMA.tables where table_schema='manager'
set @counter=@@rowcount
while @counter>0
begin
SET @sqlscript =(select ' sp_changeobjectowner manager.''' + table_name + ''', ''db0'''
from #temp where @counter=num)
execute(@sqlscript)
set @counter=@counter-1
END
-
Replace the Set statement with this one:
SET
@sqlscript =(select ' sp_changeobjectowner ''manager.' + table_name + ''', ''db0'''
from
#temp where @counter=num)
-
Worked great Thanks .
Do you have directions so I could read how those work or would you mind to explain
-
It is very straight forward and simple......two ticks together within a string translate to a single tick in the results.....it just looks difficult....
So 'Hello '' People' would turn out as Hello ' People
Or '''Hello '' People''' would turn out as 'Hello ' People'
etc...
whats much more fun is when you create dynamic sql to put ticks in the results......and end up having 5 or 6 ticks together to make the three you need for the select to show correctly when it executes to make the select....
-
Using CHR(39) might be a little cleaner than tripping ticks. It is more readable as well.
For Example:
select char(39) + 'TEST STRING' + char(39)
returns: 'TEST STRING'
-Mike DiRenzo
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply