January 9, 2012 at 10:45 am
Hi friends,
I need to drop and recreate check constraints in a few databases and would need to script this so it can be run over different databases at customer's end. Below is the sql I need to run to drop check constraints
SELECT
'ALTER TABLE ' +
QuoteName(OBJECT_NAME(so.parent_obj)) +
CHAR(10) +
' DROP CONSTRAINT ' +
QuoteName(CONSTRAINT_NAME)
FROM
INFORMATION_SCHEMA.CHECK_CONSTRAINTS cc
INNER JOIN sys.sysobjects so
ON cc.CONSTRAINT_NAME = so.[name]
In Oracle, I can spool the results of the above sql to a file and run the file to drop the constraints.. Is there a way to do the same in SQL Server? Please give me your thoughts
Thank you
January 9, 2012 at 10:51 am
You could output to a file and run that. Any reason to do so instead of just executing it as dynamic SQL?
If you really insist on the output a file step, you could do the whole thing in a PowerShell script, or something like that. Or write that query as a View and use BCP to output it then use PowerShell to run it.
Easier would be to create a cursor for that Select statement, then step through it and execute each line as a command.
Or if you can be sure it'll all fit in one command, use the For XML Path('') trick to turn it into a single statement and execute it all at once. I'd use a cursor, though. Easier and more reliable.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
December 24, 2021 at 2:23 am
Hi,
Replace the SELECT 'select table_name with your own select alter table.
IF OBJECT_ID('TEMPDB..#COMMAND_LIST') IS NOT NULL
DROP TABLE #COMMAND_LIST
CREATE TABLE #COMMAND_LIST
(ID INT IDENTITY,
COMMAND VARCHAR(200))
INSERT INTO #COMMAND_LIST (COMMAND)
SELECT 'select table_name from information_schema.tables where table_name = ''' + table_name + ''''
from information_schema.tables
--SELECT 'ALTER TABLE ' +
--QUOTENAME(OBJECT_NAME(SO.PARENT_OBJ)) +
--CHAR(10) +
--' DROP CONSTRAINT ' +
--QUOTENAME(CONSTRAINT_NAME)
--FROM
--INFORMATION_SCHEMA.CHECK_CONSTRAINTS CC
--INNER JOIN SYS.SYSOBJECTS SO
--ON CC.CONSTRAINT_NAME = SO.[NAME]
DECLARE @LOOP INT,
@MAX_LOOP INT,
@SQL VARCHAR(200)
SELECT @MAX_LOOP = MAX(ID) FROM #COMMAND_LIST
set @Loop = 1
WHILE @LOOP <= @MAX_LOOP
BEGIN
SELECT @SQL = COMMAND FROM #COMMAND_LIST WHERE ID = @LOOP
--EXECUTE(@SQL)
PRINT @SQL
SET @LOOP = @LOOP + 1
END
Jamie Ingram
September 6, 2022 at 7:00 am
This was removed by the editor as SPAM
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply