Passing a local variable to an Alter table

Question

Post reply

Passing a local variable to an Alter table

chris.murray58

SSC Enthusiast

Points: 182
More actions
June 15, 2010 at 4:25 am

#222860

Hi
I have a list of tables that I want to alter.
How can I pass a variable (table name) to an Alter Tables
Example
ALTER TABLE @MyTable NOCHECK CONSTRAINT ALL
I keep getting errors
Many Thanks

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Cadavre SSC-Forever Points: 41690 More actions · Answer 1

I found this script awhile ago that dynamically alters tables

--ALTER TABLES SCRIPT

--change the line marked *** to suit your update needs....

-- Jamie Moffat, 4/2/02

DECLARE tablenamescursor CURSOR FOR

--***

SELECT 'ALTER TABLE [' + o.name + '] ADD TimeStamp smalldatetime NOT NULL

CONSTRAINT AddDateDflt' + CAST(o.id AS VARCHAR(10)) + ' DEFAULT getdate() WITH VALUES'

-- removes the TimeStamp column, and constraint from all tables

--select 'ALTER TABLE ['+ o.name+ '] DROP CONSTRAINT AddDateDflt'+ cast(o.id as varchar(10)) + '

--ALTER TABLE ['+ o.name+ '] DROP COLUMN TimeStamp'

FROM sysobjects o,

sysindexes i

WHERE i.id = o.id

AND indid IN( 0, 1 )

AND o.name NOT LIKE 'sys%'

AND o.name NOT LIKE 'dt%'

ORDER BY o.name

OPEN tablenamescursor

DECLARE @alterTableSql NVARCHAR(400)

FETCH NEXT FROM tablenamescursor INTO @alterTableSql

WHILE ( @@FETCH_STATUS <> -1 )

BEGIN

IF ( @@FETCH_STATUS <> -2 )

BEGIN

PRINT @alterTableSql

EXEC Sp_executesql @alterTableSql

END

FETCH NEXT FROM tablenamescursor INTO @alterTableSql

END

CLOSE tablenamescursor

DEALLOCATE tablenamescursor

Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Adi Cohn-120898 SSC-Dedicated Points: 33944 More actions · Answer 2

This can not be done this way. You can use dynamic SQL and then run the string that you created. Another way is to create a select query that creates the alter statement on the tables, run it and then copy the results to the query window and run it. Here is an example of such query

select 'alter table ' + name + ' nocheck constraint all'

from sys.objects

where type = 'U' and create_date > '20100601'

Adi

--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/