sql - Update tables with same name different schemas -
How can I update all the table tables with a specific name, using SQL Server and TSQL, but how can I update different schemas under a single database? ?
: Update:
Updates include adding / deleting / modifying columns as well as changes in specific values of rows
I enable to deploy Want to be When using the SQL Server Management Studio Table Designer, when I make a change and save it in all schemas, I get permission to save a script that can be used to apply those changes in a different table. Can be done for.
: Update 2:
The reason for this request is that I want to make sure that I want to make sure that I use that script on all scripts on that script It is easy to update a specific table in many schemas, in the future, your application will definitely change and if I think it is difficult or disabled then the schema is to be updated. Will choose to use
Below I've tried to do.
announcement SOCEMA.TABLES information @update_sql NVARCHAR (max) DECLARE @table_name VARCHAR (45) DECLARE c_tables select cursor t.table_name T where t.table_name = 'TestTable' and Close t.table_catalog = 'MyDataBase' OPEN c_tables FETCH C_tables @table_name WHILE @@ Fetch_Status = 0 BEGIN SET @update_sql = N'ALTER table '+ @ table_name +' ADD column three Nvrrchar (50) NULL 'EXEC SP_executesql @update_sql END C_tables DEALLOCATE c_tables
Running this generates the following error.
The table is present and I'am
dbo Code> which is the schema owner.
I also saw that the query takes only 7 tables in the database and running with the 3 schemas used on those tables ometime whether it is the owner of each schema to log in and run the normal script Would be more efficient? Each schema owner will have access to only their respective schemas and tables.
On SQL Server 2005+, you use a database to get a list of tables can:
select t.table_name, INFORMATION_SCHEMA.TABLES T where t.table_name = 'Table_1' and t.table_catalog = 'your_database from t.table_schema'
but what needs to update did not specify - DML or DDL ...
update
announcement @update_sql Anviacaaraar (Max) DECKLARE @table_name VARCHAR (45) DECLARE c_tables Select for cursor Suggestions for t.table_name Received from science. Tiabls T WHERE t.table_name = 'Table_1' and t.table_catalog = 'your_database' OPEN c_tables FETCH c_tables @table_name WHILE @@ Fetch_Status = 0 set start @update_sql = N'UPDATE @table_name set Itable ... 'EXEC sp_executesql @ update_sql SET @update_sql = N'ALTER table @table_name ... 'EXEC sp_executesql @update_sql completion of closing c_tables DEALLOCATE c_tables