title: Altering Multiple Tables at once
body: <p>I'm trying to alter multiple SQL Server 2008 R2 tables at one time. </p>
  
  <p>This is my code:</p>
  
  <pre><code>use DatabaseName
  go
  
  Declare @SchemaUsed varchar(20) = 'dbo'
  
  create table #Tables
  (
    TableName varchar(100), Processed int
  )
  
  insert into #Tables 
    select top 1 table_name, 0  
    from INFORMATION_SCHEMA.TABLES 
    where TABLE_SCHEMA = @SchemaUsed
    and table_type = 'Base Table'
    and (TABLE_NAME like 'PM%' )
    ORDER BY TABLE_NAME
  
  DECLARE @TableName varchar(max)
  DECLARE @SQL varchar(max)
  
  WHILE EXISTS (select top 1 'x' from #Tables where Processed = 0)
  BEGIN
      SET @TableName = (select top 1 TableName from #Tables where Processed = 0)
  
    Set @SQL = 'ALTER TABLE ' + @SchemaUsed + '.' + @TableName + ' ADD [identityID]  bigint IDENTITY(1, 1) NOT NULL '
   -- Set @SQL =     '''' + @SQL + ''''
    Print @SQL
   EXEC  @SQL;
  
      update #Tables
      set Processed = 1
      where TableName = @TableName
  END
  
  drop table #Tables
  </code></pre>
  
  <p>I can't get this to work to save my life and get the following error:</p>
  
  <blockquote>
    <p>Lookup Error - SQL Server Database Error: The name 'ALTER TABLE
    dbo.PM1GTVLV ADD [identityID]  bigint IDENTITY(1, 1) NOT NULL ' is not
    a valid identifier.</p>
  </blockquote>
  
  <p>I've also tried multiple string variations and using <code>sp_executesql</code> as well. </p>
  
  <p>Can someone point out where I've gone wrong? </p>