(For example) I want to add a "DateStamp" column to all of my tables, in one hit. The following query provides me with a list of those tables that are missing the column...

SELECT [NAME]
FROM sysobjects
WHERE [TYPE] = 'U'
AND id NOT IN
(
    SELECT SO.[ID]
    FROM sysobjects SO
    JOIN syscolumns SC ON SC.ID = SO.ID
    WHERE SC.[NAME] = 'DateStamp'
)

And I am aware that I can use this in the following format:

WHILE (SELECT COUNT(*)) > 0
BEGIN
  /*LOOP*/
END

In which I know how to word the "SELECT COUNT(*)" to create the loop and I also know the code the first part of the BEGIN to capture the table names into a variable, with:

SELECT TOP 1 @table = [name].....

But, that then doesn't work with:

ALTER TABLE @table ADD 'DateStamp', etc.,


Could someone point me in the right direction here, preferably for a simple solution (as I know that you can completely control a database from within the sys... tables, but I am cautious of how easy it would be to make a mistake there) ?

Regards,

Sean Anderson

__________________
Regards,

Sean Anderson

You are probably going to have to use the execute statement, so:

exec "ALTER TABLE @table ADD 'DateStamp'"

this will evaluate the @table variable

================================================== =========
Read this if you want to know how to get a correct reply for your question:
http://www.catb.org/~esr/faqs/smart-questions.html
^^Took that from planoie's profile^^
^^Modified text taken from gbianchi profile^^
================================================== =========
Technical Editor for: Professional Search Engine Optimization with ASP.NET
http://www.wiley.com/WileyCDA/WileyT...470131470.html

Discussion:
http://p2p.wrox.com/topic.asp?TOPIC_ID=56429

When I enter the following:

CREATE PROCEDURE ADD_FIELD (@table varchar(255), @field varchar(255), @type varchar(50)) AS
EXEC "ALTER TABLE @table ADD @field @type"

SQL Query Analyzer reports:

Cannot add rows to sysdepends for the current stored procedure because it depends on the missing object 'ALTER TABLE @table ADD @field @type'. The stored procedure will still be created.

When I execute the procedure with:

ADD_FIELD 'test', 'c2', 'varchar'

It reports:

Could not find stored procedure 'ALTER TABLE @table ADD @field @type'

I also tried the same procedure, but with single quotes and that wouldn't create the stored procedure at all.

It was closer with:

CREATE PROCEDURE ADD_FIELD (@table varchar(255), @field varchar(255), @type varchar(50)) AS
DECLARE @test varchar(255)
SELECT @test = 'ALTER TABLE ' + @table + ' ADD ' + @field + ' ' + @type
EXEC @test

which returned

Could not find stored procedure 'ALTER TABLE test ADD c2 varchar'

Regards,

Sean Anderson

Voila!

It's amazing what you can stumble across. Here's the first test, but sure shows that it can be done.

DECLARE @statement nvarchar(255)

SELECT @statement = 'SELECT * FROM TEST'

EXECUTE sp_executesql @statement

If you want more on this, check Google for "sp_executesql" or Dynamic SQL.

Regards,

Sean Anderson

http://www.sommarskog.se/dynamic_sql.html