D
I've done this for MS SQL (prob the same would port to other databases)
using stored procs
Firstly I tried to do it from a triggers on a change log file but when I
finished the code and tested it i got the error message
"Cannot execute DDL Statements inside trigger" - darn
Add goes like
CREATE PROC AddCol
@TableName varchar(80),
@ColumnName varchar(80),
@DataType varchar(80),
@DefaultValue varchar(80),
@ConstraintText varchar(255)
AS
BEGIN
DECLARE
@SQLOptions varchar(255)
SET @SQLOptions = ' '
IF (NOT @DefaultValue IS NULL) SET @SQLOptions = @SQLOptions+' DEFAULT
'+@DefaultValue
IF (NOT @ConstraintText IS NULL) SET @SQLOptions = @SQLOptions+'
'+@ConstraintText
EXECUTE ( 'ALTER TABLE '+@TableName+' ADD ['+@ColumnName + ']
'+@DataType+@SQLOptions)
IF @@ERROR <> 0 RETURN 1
END
-- Sucess
RETURN 0
END
Dropping is a little more complex as you have to drop constraints first
CREATE PROC spSMDropPropCol
@TableName sysname,
@ColName sysname,
AS
BEGIN
DECLARE
@DefaultValue sysname,
@ErrorText VARCHAR(255),
@ErrorCode VARCHAR(255),
@SQL VARCHAR(8000),
@ConName sysname,
@Result INT,
@CreateText sysname
SET @ErrorText = 'Done'
SET @ErrorCode = 0;
-- Drop any constraints assoc with this column
DECLARE ConstraintCursor CURSOR
FOR SELECT ConObj.Name
FROM sysobjects ConObj JOIN
sysobjects TabObj ON ConObj.parent_obj = TabObj.ID JOIN
syscolumns Col ON Col.ID = ConObj.parent_obj and Col.ColID =
ConObj.Info
WHERE (ConObj.xtype = 'C' or ConObj.xtype = 'PK' or ConObj.xtype =
'D')
and TabObj.Name = @TableName
OPEN ConstraintCursor
FETCH NEXT FROM ConstraintCursor
INTO @ConName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @SQL = 'ALTER TABLE '+@TableName+' DROP ['+@ConName+']'
EXEC(@SQL)
FETCH NEXT FROM ConstraintCursor
INTO @ConName
END
CLOSE ConstraintCursor
DEALLOCATE ConstraintCursor
-- Make the alter table script for this column
SET @SQL = 'ALTER TABLE '+@TableName+' DROP COLUMN ['+@PropColName+']'
EXEC(@SQL)
END
----- Original Message -----
From: Donovan J. Edye <[EMAIL PROTECTED]>
To: Multiple recipients of list delphi <[EMAIL PROTECTED]>
Sent: Thursday, 12 October 2000 12:36
Subject: [DUG]: [Q] Table Structures....
> G'Day,
>
> Given a table definition that is subject to change, has anyone got any
code
> or advice on how the tables in the database could be dynamically
> restructured based on the table definition? All this would be done via ADO
> and should be back end independent. An example:
>
> - Initial Release
>
> CREATE TABLE tblMine
> (
> A VarChar(20)
> )
>
> - 6 mths down the track say the table may need to be
>
> CREATE TABLE tblMine
> (
> A VarChar(25),
> B SmallInt
> )
>
>
> ------------------------------------------------------------------------
> --Donovan [[EMAIL PROTECTED]]
> Donovan J. Edye [www.edye.wattle.id.au]
> Namadgi Systems, Delphi Developer [www.namsys.com.au]
> Voice: +61 2 6285-3460 Fax: +61 2 6285-3459
> TVisualBasic = Class(None);
> Heard just before the 'Big Bang': "...Uh Oh...."
> ------------------------------------------------------------------------
> GXExplorer [http://www.gxexplorer.org] Freeware Windows Explorer
> replacement. Also includes freeware delphi windows explorer components.
> ------------------------------------------------------------------------
>
> --------------------------------------------------------------------------
-
> New Zealand Delphi Users group - Delphi List - [EMAIL PROTECTED]
> Website: http://www.delphi.org.nz
> To UnSub, send email to: [EMAIL PROTECTED]
> with body of "unsubscribe delphi"
>
---------------------------------------------------------------------------
New Zealand Delphi Users group - Delphi List - [EMAIL PROTECTED]
Website: http://www.delphi.org.nz
To UnSub, send email to: [EMAIL PROTECTED]
with body of "unsubscribe delphi"
