Possible Duplicate:
How do I escape a single quote in sqlserver?
I got a script below that drop everything on the database from this link. It does error when I execute on this line.
SET @statement = '
IF(@type = 'F') or (@type = 'C') or (@type = 'D') or (@type='F') or (@type='K')
The reason is because the single quote. I want to know how can I fix this error?
/*** drop (pretty much) everything before rebuilding the database ***/
DECLARE
OBJECTS CURSOR FOR SELECT
so.name,
so.type,
so.type_desc,
p.name AS parentName
FROM
sys.objects AS so
LEFT JOIN sys.objects AS p ON so.parent_object_id = p.object_id
WHERE
so.schema_id = 1
ORDER BY
CASE
WHEN so.type = 'F' THEN
0
WHEN so.type = 'TR' THEN
1
WHEN so.type = 'U' THEN
2
WHEN so.type = 'F' THEN
3
ELSE
4
END OPEN OBJECTS DECLARE
@name AS nvarchar (MAX) DECLARE
@type AS nvarchar (2) DECLARE
@type_desc AS nvarchar DECLARE
@parentName AS nvarchar (MAX) DECLARE
@statement AS nvarchar (MAX) FETCH NEXT
FROM
OBJECTS INTO @name,
@type,
@type_desc,
@parentName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @statement = ' IF(@type = ' F ')
BEGIN
PRINT ' DROPING FK : ' + @name + ' OF type ' + @type + ' (' + @type_desc + ') '
SET @statement = ' ALTER TABLE ' + @parentName + ' DROP CONSTRAINT ' + @name
EXECUTE(@statement)
END
ELSE IF (@type = ' TR ')
BEGIN
PRINT ' DROPING TRIGGER : ' + @name + ' OF type ' + @type + ' (' + @type_desc + ') '
SET @statement = ' DROP TRIGGER ' + @name
EXECUTE(@statement)
END
ELSE IF (@type = ' U ')
BEGIN
PRINT ' DROPING TABLE : ' + @name + ' OF type ' + @type + ' (' + @type_desc + ') '
SET @statement = ' DROP TABLE ' + @name
EXECUTE(@statement)
END
ELSE IF (@type = ' FN ')
BEGIN
PRINT ' DROPING FUNCTION : ' + @name + ' OF type ' + @type + ' (' + @type_desc + ') '
SET @statement = ' DROP FUNCTION ' + @name
EXECUTE(@statement)
END
ELSE
PRINT ' Didn 't drop object ' + @name + ' of type ' + @type + ' (' + @type_desc + ')' FETCH NEXT
FROM
OBJECTS INTO @name,
@type,
@type_desc,
@parentName
END CLOSE OBJECTS DEALLOCATE OBJECTS
