jbclelland
asked on
Altering tables using SQL statements
Hi there,
We have a web based product written in php utilizing a MS SQL database. We have clients that host our software internally meaning when we have to send them updates to our software we are not able see their version of the database, meaning if one of our updates includes a database change we have to send them an update script as well that will do this.
That's all be fine up to now, however a bug has been discovered where the Precision of a numeric field was incorrect and so we have written an SQL statment that will change the precision of the field:
ALTER TABLE tablename ALTER COLUMN field_name DOUBLE PRECISION(5);
However we get an error:
The object 'DF__actionpla__statu__070 20F21' is dependent on column 'status_percentage_complet e'
We obviously need to remove this dependancy before executing the first statement, but we can't work out how to do this using an SQL statement.
Help much apreciated, and time is of the escence so maximum points available.
Tom
We have a web based product written in php utilizing a MS SQL database. We have clients that host our software internally meaning when we have to send them updates to our software we are not able see their version of the database, meaning if one of our updates includes a database change we have to send them an update script as well that will do this.
That's all be fine up to now, however a bug has been discovered where the Precision of a numeric field was incorrect and so we have written an SQL statment that will change the precision of the field:
ALTER TABLE tablename ALTER COLUMN field_name DOUBLE PRECISION(5);
However we get an error:
The object 'DF__actionpla__statu__070
We obviously need to remove this dependancy before executing the first statement, but we can't work out how to do this using an SQL statement.
Help much apreciated, and time is of the escence so maximum points available.
Tom
Continuing from the above post ,
Run
sp_help 'urtablename'
first to see the constraints, then delete the constraints
,Once the constraints are deleted , dro p the table
Run
sp_help 'urtablename'
first to see the constraints, then delete the constraints
,Once the constraints are deleted , dro p the table
ASKER
Is the name 'DF__actionpla__statu__070 20F21' always going to be consistant or could the name of it vary dependant on installation etc because we need to send this out to several clients. If it could differ, can we search for all the depandcies of a particular table?
Tom
Tom
No, it may change. Because you are rolling this out to several clients you should make it a practice to always name the constraints - don't let SQL do it for you. You can do that in the CREATE TABLE statements.
I found this script here - http://www.sqlteam.com/item.asp?ItemID=2232 - and it works well in identifying the constraints.
create view v_DEFAULT_CONSTRAINT
as select db_name() as CONSTRAINT_CATALOG ,t_obj.name as TABLE_NAME ,
user_name(c_obj.uid) as CONSTRAINT_SCHEMA ,c_obj.name as CONSTRAINT_NAME ,
col.name as COLUMN_NAME ,col.colid as ORDINAL_POSITION ,com.text as DEFAULT_CLAUSE
from sysobjects c_obj join syscomments com on c_obj.id = com.id join sysobjects t_obj
on c_obj.parent_obj = t_obj.id join sysconstraints con on c_obj.id = con.constid
join syscolumns col on t_obj.id = col.id and con.colid = col.colid where c_obj.uid = user_id()
and c_obj.xtype = 'D'
I found this script here - http://www.sqlteam.com/item.asp?ItemID=2232 - and it works well in identifying the constraints.
create view v_DEFAULT_CONSTRAINT
as select db_name() as CONSTRAINT_CATALOG ,t_obj.name as TABLE_NAME ,
user_name(c_obj.uid) as CONSTRAINT_SCHEMA ,c_obj.name as CONSTRAINT_NAME ,
col.name as COLUMN_NAME ,col.colid as ORDINAL_POSITION ,com.text as DEFAULT_CLAUSE
from sysobjects c_obj join syscomments com on c_obj.id = com.id join sysobjects t_obj
on c_obj.parent_obj = t_obj.id join sysconstraints con on c_obj.id = con.constid
join syscolumns col on t_obj.id = col.id and con.colid = col.colid where c_obj.uid = user_id()
and c_obj.xtype = 'D'
ASKER
Thanks for that, however could you please give me some more information as to how I can use this code to help me out of the problem I have?
Tom.
Tom.
Ok, I found a script here:http://midwestcoders.net/blogs/matt_mikulicz/archive/2005/11/10/183.aspx
It changes the default name to table and column name and drops the hex numbers at the end. I changed it to work through every table in a 2000 database (I would add the schema name to use it with 2005). You run this script and it will generate a text script - copy and paste it into a new QA and then run it to change the default names.
set quoted_identifier off
DECLARE
@Exists VARCHAR(200),
@Begin VARCHAR(100),
@Print1 VARCHAR(500),
@Drop VARCHAR(200),
@Print2 VARCHAR(500),
@Add VARCHAR(200),
@End VARCHAR(100),
@TABLE_NAME VARCHAR(128)
DECLARE TN CURSOR FOR
SELECT table_name FROM information_schema.COLUMNS
WHERE column_default IS NOT NULL
--SELECT @TABLE_NAME = 'OpenTransactionsHistory'
OPEN TN
FETCH NEXT FROM TN INTO @TABLE_NAME
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE AlterDefault_Cursor CURSOR FOR
SELECT
"if exists(select * from dbo.sysobjects where id = object_id(N'[dbo].[" + c_obj.name + "]') and OBJECTPROPERTY(id, N'IsDefaultCnst') = 1)" AS [exists],
'BEGIN' AS [begin],
"PRINT ' Dropping " + c_obj.name + "'" AS [Print1],
'ALTER TABLE [dbo].[' + t_obj.name + '] DROP CONSTRAINT ' + c_obj.name + '' AS [Drop],
"PRINT ' Creating DF_" + t_obj.name + '_' + col.name + "'" AS [Print2],
'ALTER TABLE [dbo].[' + t_obj.name + '] ADD CONSTRAINT DF_' + t_obj.name + '_' + col.name + ' DEFAULT' + com.text + ' FOR ' + col.name + '' AS [Add],
'END' AS [end]
FROM sysobjects c_obj
INNER JOIN syscomments com ON c_obj.id = com.id
INNER JOIN sysobjects t_obj ON c_obj.parent_obj = t_obj.id
INNER JOIN sysconstraints con ON c_obj.id = con.constid
INNER JOIN syscolumns col ON t_obj.id = col.id
AND con.colid = col.colid
WHERE c_obj.xtype = 'D'
AND t_obj.name = @TABLE_NAME
OPEN AlterDefault_Cursor
FETCH NEXT FROM AlterDefault_Cursor
INTO @Exists, @Begin, @Print1, @Drop, @Print2, @Add, @End
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @Exists
PRINT ' ' + @Begin
PRINT ' ' + "PRINT ''"
PRINT ' ' + @Print1
PRINT ' ' + @Drop
PRINT ' ' + @Print2
PRINT ' ' + @Add
PRINT ' ' + @End
PRINT ''
FETCH NEXT FROM AlterDefault_Cursor
INTO @Exists, @Begin, @Print1, @Drop, @Print2, @Add, @End
END
CLOSE AlterDefault_Cursor
DEALLOCATE AlterDefault_Cursor
FETCH NEXT FROM TN INTO @TABLE_NAME
END
CLOSE TN
DEALLOCATE TN
set quoted_identifier on
It changes the default name to table and column name and drops the hex numbers at the end. I changed it to work through every table in a 2000 database (I would add the schema name to use it with 2005). You run this script and it will generate a text script - copy and paste it into a new QA and then run it to change the default names.
set quoted_identifier off
DECLARE
@Exists VARCHAR(200),
@Begin VARCHAR(100),
@Print1 VARCHAR(500),
@Drop VARCHAR(200),
@Print2 VARCHAR(500),
@Add VARCHAR(200),
@End VARCHAR(100),
@TABLE_NAME VARCHAR(128)
DECLARE TN CURSOR FOR
SELECT table_name FROM information_schema.COLUMNS
WHERE column_default IS NOT NULL
--SELECT @TABLE_NAME = 'OpenTransactionsHistory'
OPEN TN
FETCH NEXT FROM TN INTO @TABLE_NAME
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE AlterDefault_Cursor CURSOR FOR
SELECT
"if exists(select * from dbo.sysobjects where id = object_id(N'[dbo].[" + c_obj.name + "]') and OBJECTPROPERTY(id, N'IsDefaultCnst') = 1)" AS [exists],
'BEGIN' AS [begin],
"PRINT ' Dropping " + c_obj.name + "'" AS [Print1],
'ALTER TABLE [dbo].[' + t_obj.name + '] DROP CONSTRAINT ' + c_obj.name + '' AS [Drop],
"PRINT ' Creating DF_" + t_obj.name + '_' + col.name + "'" AS [Print2],
'ALTER TABLE [dbo].[' + t_obj.name + '] ADD CONSTRAINT DF_' + t_obj.name + '_' + col.name + ' DEFAULT' + com.text + ' FOR ' + col.name + '' AS [Add],
'END' AS [end]
FROM sysobjects c_obj
INNER JOIN syscomments com ON c_obj.id = com.id
INNER JOIN sysobjects t_obj ON c_obj.parent_obj = t_obj.id
INNER JOIN sysconstraints con ON c_obj.id = con.constid
INNER JOIN syscolumns col ON t_obj.id = col.id
AND con.colid = col.colid
WHERE c_obj.xtype = 'D'
AND t_obj.name = @TABLE_NAME
OPEN AlterDefault_Cursor
FETCH NEXT FROM AlterDefault_Cursor
INTO @Exists, @Begin, @Print1, @Drop, @Print2, @Add, @End
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @Exists
PRINT ' ' + @Begin
PRINT ' ' + "PRINT ''"
PRINT ' ' + @Print1
PRINT ' ' + @Drop
PRINT ' ' + @Print2
PRINT ' ' + @Add
PRINT ' ' + @End
PRINT ''
FETCH NEXT FROM AlterDefault_Cursor
INTO @Exists, @Begin, @Print1, @Drop, @Print2, @Add, @End
END
CLOSE AlterDefault_Cursor
DEALLOCATE AlterDefault_Cursor
FETCH NEXT FROM TN INTO @TABLE_NAME
END
CLOSE TN
DEALLOCATE TN
set quoted_identifier on
ASKER
Thanks once again, however I do not have access to a client's machine, and we do not want them to see things like this, so I really need a solution that will work from a web based environment rather than using Query Analyser or Enterprise Manager.
Tom
Tom
Oh, sorry. You might have a better chance of finding a front-end web solution using the particular web area or language area. This section deals with writing t-sql queries and database maintenance questions.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
TRY IT
ALTER TABLE [dbo].[acct_Contacts] DROP CONSTRAINT [DF__actionpla__statu__070
GO
ALTER TABLE tablename ALTER COLUMN field_name DOUBLE PRECISION(5);
GO