Update Database Structure
-
Hi to all.
It's the first time i write here. I don't know well SQL Server and i need a hand.
I have a database with table, index, store procedure ecc. and i install this database in many server.
Now, if i update a table in my database, for example i add a column or if i add a table to database, or if i change the column type, i want generate a script for automatically update all other server.
If possible, i wold like a script who create a script for update other server from my original database.
The script must don't drop the table or column, but for example if column exist the script must alter the column.
I don't want lost data in other server, only update all structure of my database.
It's possible?
Anyone have do anything about this?
I have search in script section of this site but i don't have find anything.
Anyone can help me?
I nedd a script because i want include the script in a setup of my application
Tnx
-
You need to write scripts to do this kind of thing.
e.g., add a int column to an existing table with default value 0:
IF NOT EXISTS(SELECT 1 FROM syscolumns WHERE ID=OBJECT_ID('YourTableName') AND name='NewColumnName')
BEGIN
ALTER TABLE YourTableName ADD NewColumnName int NOT NULL
CONSTRAINT DF_xxx DEFAULT (0)
END
GO
I remember there is tool SQLComp to compare the schema of two databases and generates updating script.
-
i need something like this:
[ ----- Begin code]
--- Script che genera uno script per la creazione/aggiornamento del database
Declare @sql varchar(8000)
DECLARE @DB1 SysName
DECLARE @DBName1 SysName
DECLARE @SvrName1 SysName
set @DB1 = 'DPC_Kernel'
set @DBName1 = 'DPC_Kernel'
set @SvrName1 = 'PC_ROBERTOA'
Create Table #TableColumns
(
TABLE_CATALOG sysname NOT NULL,
TABLE_SCHEMA sysname NOT NULL,
TABLE_NAME sysname NOT NULL,
COLUMN_NAME sysname NOT NULL,
ORDINAL_POSITION smallint NOT NULL,
COLUMN_DEFAULT nvarchar(2000) NULL,
IS_NULLABLE bit NOT NULL,
DATA_TYPE sysname NOT NULL,
CHARACTER_MAXIMUM_LENGTH int NULL,
CHARACTER_OCTET_LENGTH int NULL,
NUMERIC_PRECISION tinyint NULL,
NUMERIC_PRECISION_RADIX smallint NULL,
NUMERIC_SCALE int NULL,
DATETIME_PRECISION smallint NULL,
IS_COMPUTED bit NOT NULL,
IS_IDENTITY bit NOT NULL,
IDENTITY_SEED int NULL,
IDENTITY_INCR int NULL,
IS_FORREPL bit NOT NULL,
IS_ROWGUID bit NOT NULL,
IS_DEFAULT bit NOT NULL,
IS_MAXIMUM bit NOT NULL,
IS_OCTET bit NOT NULL,
IS_PRECISION bit NOT NULL,
IS_RADIX bit NOT NULL,
IS_SCALE bit NOT NULL,
IS_DATETIME bit NOT NULL
)
-- Get the tables with column data
set @sql = 'Insert into #TableColumns
SELECT '''+@DB1 +''',
usr.name, obj.name,
Col.name,
col.colid,
com.text,
col.isnullable,
spt_dtp.LOCAL_TYPE_NAME,
convert(int, OdbcPrec(col.xtype, col.length, col.xprec) + spt_dtp.charbin),
convert(int, spt_dtp.charbin +
case when spt_dtp.LOCAL_TYPE_NAME in (''nchar'', ''nvarchar'', ''ntext'')
then 2*OdbcPrec(col.xtype, col.length, col.xprec)
else OdbcPrec(col.xtype, col.length, col.xprec)
end),
nullif(col.xprec, 0),
spt_dtp.RADIX,
col.scale,
spt_dtp.SQL_DATETIME_SUB,
col.iscomputed,
col.colstat,
NULL,
NULL,
0,
0,
0,
0,
0,
0,
0,
0,
0
from ['+@SvrName1+'].['+@DBName1+'].dbo.sysobjects obj,
['+@SvrName1+'].master.dbo.spt_datatype_info spt_dtp,
['+@SvrName1+'].['+@DBName1 +'].dbo.systypes typ,
['+@SvrName1+'].['+@DBName1 +'].dbo.sysusers usr,
['+@SvrName1+'].['+@DBName1 +'].dbo.syscolumns col
LEFT OUTER JOIN
['+@SvrName1+'].['+@DBName1 +'].dbo.syscomments com on col.cdefault = com.id
AND com.colid = 1
WHERE
obj.id = col.id
AND obj.uid=usr.uid
AND typ.xtype = spt_dtp.ss_dtype
AND (spt_dtp.ODBCVer is null or spt_dtp.ODBCVer = 2)
AND obj.xtype in (''U'', ''V'')
AND col.xusertype = typ.xusertype
AND (spt_dtp.AUTO_INCREMENT is null or spt_dtp.AUTO_INCREMENT = 0)'
Execute (@SQL)
SET @sql ='UPDATE #TableColumns SET IDENTITY_SEED =A.seed, IDENTITY_INCR = A.Incr
From
(Select table_Schema=usr.name, table_name=obj.name,
Seed =ident_seed(''['+@DBName1+'].''+usr.Name+''.''+obj.name),
Incr =ident_incr(''['+@DBName1+'].''+usr.name+''.''+obj.name)
From [' + @SvrName1 + '].['+@DBName1+'].dbo.sysobjects obj,
[' + @SvrName1 + '].['+@DBName1+'].dbo.sysusers usr
WHERE obj.uid = usr.uid and
ident_seed(''['+@DBName1+'].''+usr.Name+''.''+obj.name) is NOT NULL) A
WHERE A.table_Schema= #TableColumns.Table_Schema and
A.table_name = #TableColumns.Table_Name and
#TableColumns.IS_IDENTITY & 1 = 1 and
#TableColumns.Table_Catalog ='''+@DB1+''''
Execute (@SQL)
Update #TableColumns SET IS_DEFAULT =1 Where COLUMN_DEFAULT IS NOT NULL
Update #TableColumns SET IS_OCTET = 1 Where CHARACTER_OCTET_LENGTH IS NOT NULL
Update #TableColumns SET IS_RADIX = 1 Where NUMERIC_PRECISION_RADIX IS NOT NULL
Update #TableColumns SET IS_MAXIMUM = 1 Where CHARACTER_MAXIMUM_LENGTH IS NOT NULL
Update #TableColumns SET IS_PRECISION = 1 Where NUMERIC_PRECISION IS NOT NULL
Update #TableColumns SET IS_SCALE = 1 Where NUMERIC_SCALE IS NOT NULL
Update #TableColumns SET IS_DATETIME = 1 Where DATETIME_PRECISION IS NOT NULL
Update #TableColumns SET COLUMN_DEFAULT ='' WHERE COLUMN_DEFAULT IS NULL
Update #TableColumns SET CHARACTER_MAXIMUM_LENGTH=0 WHERE CHARACTER_MAXIMUM_LENGTH is NULL
Update #TableColumns SET CHARACTER_OCTET_LENGTH=0 WHERE CHARACTER_OCTET_LENGTH IS NULL
Update #TableColumns SET NUMERIC_PRECISION =0 WHERE NUMERIC_PRECISION IS NULL
Update #TableColumns SET NUMERIC_PRECISION_RADIX = 0 WHERE NUMERIC_PRECISION_RADIX IS NULL
Update #TableColumns SET NUMERIC_SCALE = 0 WHERE NUMERIC_SCALE is NULL
Update #TableColumns SET DATETIME_PRECISION=0 WHERE DATETIME_PRECISION is NULL
declare @TABLE_CATALOG sysname
declare @TABLE_SCHEMA sysname
declare @TABLE_NAME sysname
declare @COLUMN_NAME sysname
declare @ORDINAL_POSITION smallint
declare @COLUMN_DEFAULT varchar(2000)
declare @IS_NULLABLE varchar(1)
declare @DATA_TYPE sysname
declare @CHARACTER_MAXIMUM_LENGTH int
declare @CHARACTER_OCTET_LENGTH int
declare @NUMERIC_PRECISION tinyint
declare @NUMERIC_PRECISION_RADIX smallint
declare @NUMERIC_SCALE int
declare @DATETIME_PRECISION smallint
declare @IS_COMPUTED bit
declare @IS_IDENTITY varchar(1)
declare @IDENTITY_SEED varchar
declare @IDENTITY_INCR varchar
declare @IS_FORREPL bit
declare @IS_ROWGUID bit
declare @IS_DEFAULT bit
declare @IS_MAXIMUM bit
declare @IS_OCTET bit
declare @IS_PRECISION bit
declare @IS_RADIX bit
declare @IS_SCALE bit
declare @IS_DATETIME bit
declare @Add_Alter varchar(15)
declare @CreoMiaColonna varchar(1000)
set @CreoMiaColonna = ''
Print '--------------------------- Inizio creazione Script ----------------------------'
Print ' ATTENZIONE: QUESTO SCRIPT ANDRA'' LANCIATO PER IL DATABASE CHE VOGLIO UPGRADATE'
Print '--------------------------------------------------------------------------------'
Print ''
-- select * from #TableColumns
-- creo un cursore per fare il ciclo (per usare poi il cursore devo dichiarare tute le variabili)
declare miocursore cursor for select * from #TableColumns order by TABLE_NAME
OPEN miocursore
FETCH NEXT FROM miocursore
WHILE @@FETCH_STATUS = 0
BEGIN
FETCH NEXT FROM miocursore INTO @TABLE_CATALOG,
@TABLE_SCHEMA,
@TABLE_NAME,
@COLUMN_NAME,
@ORDINAL_POSITION,
@COLUMN_DEFAULT,
@IS_NULLABLE,
@DATA_TYPE,
@CHARACTER_MAXIMUM_LENGTH,
@CHARACTER_OCTET_LENGTH,
@NUMERIC_PRECISION,
@NUMERIC_PRECISION_RADIX,
@NUMERIC_SCALE,
@DATETIME_PRECISION,
@IS_COMPUTED,
@IS_IDENTITY,
@IDENTITY_SEED,
@IDENTITY_INCR,
@IS_FORREPL,
@IS_ROWGUID,
@IS_DEFAULT,
@IS_MAXIMUM,
@IS_OCTET,
@IS_PRECISION,
@IS_RADIX,
@IS_SCALE,
@IS_DATETIME
-- Per ogni riga controllo che esista la tabella e s enon esiste la creo
-- e poi in base alla colonna controllo se esiste.
-- se non esiste la colonna la creo altrimenti faccio un alter table
-- Se non esiste creo la tabella e visto che non posso crearla vuota, creo anche un campo che poi cancellerò
-- P.s. Nella creazione della tabella ometto volutamente l'ON e TEXTIMAGE_ON perchè visto che voglio
-- che i dati siano sempre sul gruppo Primary non mi interessa specificare cose diverse
Print 'IF NOT EXISTS (select * from dbo.sysobjects where id = object_id(N''[dbo].[FK_Consys_'+@TABLE_NAME+']'') and OBJECTPROPERTY(id, N''IsForeignKey'') = 1)'
Print ' CREATE TABLE [dbo].['+@TABLE_NAME+'] ([_setupTemp] [int] IDENTITY (1, 1) NOT NULL ) '
-- Adesso controllo le colonne
Print ' Set @Add_Alter = '' ALTER COLUMN '' '
Print ' IF NOT EXISTS(SELECT 1 FROM syscolumns WHERE ID=OBJECT_ID('''+@TABLE_NAME+''') AND name='''+@COLUMN_NAME+''')'
Print ' Set @Add_Alter = '' ADD '' '
Print ' BEGIN '
-- qui gestisco il tipo dati perchè la creazione e/o modifica varia in base al tipo
set @CreoMiaColonna = 'ALTER TABLE '+@TABLE_NAME+' +@Add_Alter+ '+@COLUMN_NAME + ' '
CASE UPPER(RTRIM(@DATA_TYPE))
WHEN 'INT'
CASE @IS_IDENTITY
WHEN '1'
Set @CreoMiaColonna = @CreoMiaColonna + ' int IDENTITY (' + @IDENTITY_SEED + ', ' + @IDENTITY_INCR + ') NOT NULL'
ELSE
BEGIN
CASE @IS_NULLABLE
WHEN '1' THEN
Set @CreoMiaColonna = @CreoMiaColonna + ' int NULL'
ELSE
Set @CreoMiaColonna = @CreoMiaColonna + ' int NOT NULL'
END --rel a IS_NULLABLE
END
END -- rel. a IS_IDENTITY
END --relativo al data_type
-- scrivo la riga per generare la colonna
Print ' ' + @CreoMiaColonna
Print ' END' -- relativo al begin
-- adesso cancello l'eventuale colonna creata per la creazione della tabella
Print ' IF NOT EXISTS(SELECT 1 FROM syscolumns WHERE ID=OBJECT_ID('''+@TABLE_NAME+''') AND name=''[_setupTemp]'')'
Print ' ALTER TABLE '+@TABLE_NAME+' DROP COLUMN [_setupTemp]'
END
CLOSE miocursore
DEALLOCATE miocursore
drop table #TableColumns -- distruggo la tabella temporanea
[----- End code]
This script create an output scipt.
If i save the output i can run the output script in another server for sincronize the database
p.s. this script don't work, it is my first scipt
-
First of all, change your code as follows:
set @sql = 'Insert into #TableColumns
SELECT '''+@DB1 +''',
Change to:
set @sql = 'SELECT '''+@DB1 +''',
When you EXEC the dynamic SQL, it cannot see the #TableColumns table as it is local in scope and the EXEC is another scope, so Errors.
Now change:
Execute (@SQL)
to:
PRINT @sql
INSERT INTO #TableColumns
EXEC (@SQL)
PRINT CONVERT(varchar, @@ERROR)
You should verify that the CREATE TABLE and SELECT columns are, equal, otherwise add the (<column list>
to the INSERT INTO in the same order as the SELECT.You do realize that the following:
OPEN miocursore
FETCH NEXT FROM miocursore
WHILE @@FETCH_STATUS = 0
Skips the 1st row in #TableColumns, right?
You state that the script does not work, care to provide the error and line number where it fails?
Indent your code to find any missing BEGIN ... END and CASE ... END blocks.
While debugging, always add the following to any dynamic SQL:
PRINT @sql
EXEC (@SQL)
PRINT CONVERT(varchar, @@ERROR)
Then when you are convinced that the dynamic SQL is OK, then:
-- PRINT @sql
EXEC (@SQL)
-- PRINT CONVERT(varchar, @@ERROR)
Leaving this debug feature for the next change you may make.
Andy
-
Have you considered that a column that is referenced by an index, a primary key or by a foreign key constraint cannot be altered ?
To correctly write a comparison tool to generate the appropriate create, alter, drop statements will take months of effort.
Recommend buying Red-Gates SQL Compare for $295.
http://www.red-gate.com/products/SQL_Compare/index.htm
You should also consider buying the bundle of SQL Compare, Data Compare and DTS Compare for $490.
SQL = Scarcely Qualifies as a Language
-
ofcourse, all are right.
Now i'm at home and i can't reply to error number and line.
I have see external tool, but i need to include a script inside the setup of my application.
I need to create or alter the table for install my application or update the database.
I have see many tool who compare 2 database, but i nedd to do this all inside the setup and i think to create a script.
But, if i change the databse, i must recreate the script, then, i have thinked to create a script who generate a script because i need to include all inside my setup.
Tomorrow i try to correct my script and i have many problem because it is my first script
I can't beleave who anyone don't have this problem but i don't know how to resolve this with other way.
For now tnx to all
-
Hello,
i know the basic using of SQL Server, and I have a problem.
I need to update the structure of one table that exist in many DataBases.
Example: I have to add a new field in DataBases A and B.
DataBase: A
Table: Customers
Fields: Id, Name, Telf
DateBase: B
Table: Customers
Fiels: Id, Name, email
I need Script to obtein next result:
DataBase: A
Table: Customers
Fields: Id, Name, Telf, Country
DateBase: B
Table: Customers
Fiels: Id, Name, email, Country
Thanks.
Regards.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply