SOWI Framework

Database Project

Click or drag to resize

SQL Scripts

SQL scripts for database handling

This topic contains the following sections:

Database handling example
SQL script snippets
Basis Scripts

Database handling example

This example basis of the Basis Scripts

Example: create a table and his columns include index and defaults values

--------------------------------------------------------------------------------
-- table: [easyPlanAppOption]
/* note:*/


EXEC TableAdd 'easyPlanAppOption'
GO

-- columns
EXEC ColumnAdd 'easyPlanAppOption', 'PublicClientID', 'int'
EXEC ColumnAdd 'easyPlanAppOption', 'WebRootUrl', 'nvarchar', '255'
GO

-- default values
EXEC ConstraintDefaultAdd 'easyPlanAppOption', 'PublicClientID', '', '0'
GO

-- indexes
EXEC IndexAdd 'easyPlanAppOption', 'ClientID'
GO

Example: delete table

EXEC TableDelete 'easyPlanAppOption'
GO

SQL script snippets

Can uses Supercharger Auto Text Manager

Screen Auto Text Manager

--------------------------------------------------------------------------------
-- table: [..P1:table name..]
/* note:        standard field                        meaning
          Name*/


EXEC TableAdd '..P1..'
GO

-- columns
EXEC ColumnAdd '..P1..', '', 'int'
GO

-- default values
EXEC ConstraintDefaultAdd '..P1..', '', '', '0'
GO

-- indexes
EXEC IndexAdd '..P1..', 'ClientID'
GO

Basis Scripts

This section contains the following subsections:

Table Add
Table Delete
Column Add
Column Remove
Column Rename
Index Add
Index Remove
Constraint Default Add
Constraint Default Remove
Constraint Default Rename

Table Add

/**** function TableAdd ****/
-- added a table with standard SOWI fields
IF (OBJECT_ID('dbo.TableAdd') IS NOT NULL)
  DROP PROCEDURE dbo.TableAdd
GO

CREATE PROCEDURE dbo.TableAdd
(
    @TableName [nvarchar](255)
)
AS
BEGIN
    IF NOT EXISTS(SELECT * FROM sys.objects
                  WHERE object_id = OBJECT_ID(N'[dbo].[' + @TableName +']') AND type in (N'U'))
    BEGIN
        Declare @SQL varchar(MAX)
        SELECT @SQL = 'CREATE TABLE [dbo].[' + @TableName + ']('
        SELECT @SQL = @SQL + '[ID] [int] IDENTITY (1, 1) NOT NULL, '
        SELECT @SQL = @SQL + '[GUID] [uniqueidentifier] NULL, '
        SELECT @SQL = @SQL + '[ClientID] [int] NULL, '
        SELECT @SQL = @SQL + '[Name] [nvarchar](255) NULL, '
        SELECT @SQL = @SQL + '[Label] [nvarchar](255) NULL default(''''), '
        SELECT @SQL = @SQL + '[Description] [ntext] NULL default(''''), '
        SELECT @SQL = @SQL + '[DateTimeValue] [datetime] NULL, '
        SELECT @SQL = @SQL + '[Value] [float] NULL default(0), '
        SELECT @SQL = @SQL + '[Text] [ntext] NULL default(''''), '
        SELECT @SQL = @SQL + '[Flag] [bit] NOT NULL default(0), '
        SELECT @SQL = @SQL + '[Checked] [bit] NOT NULL default(0), '
        SELECT @SQL = @SQL + '[Selection] [int] NULL default(0), '
        SELECT @SQL = @SQL + '[ActionText] [nvarchar](255) NULL default(''''), '
        SELECT @SQL = @SQL + '[TaskText] [nvarchar](255) NULL default(''''), '
        SELECT @SQL = @SQL + '[StatusText] [nvarchar](255) NULL default(''''), '
        SELECT @SQL = @SQL + '[CreateDate] [datetime] NULL, '
        SELECT @SQL = @SQL + '[CreateUser] [nvarchar](255) NULL, '
        SELECT @SQL = @SQL + '[LastEditDate] [datetime] NULL, '
        SELECT @SQL = @SQL + '[LastEditUser] [nvarchar](255) NULL, '
        SELECT @SQL = @SQL + '[DeleteDate] [datetime] NULL, '
        SELECT @SQL = @SQL + '[DeleteUser] [nvarchar](255) NULL, '
        SELECT @SQL = @SQL + '[Archive] [bit] NOT NULL default(0), '
        SELECT @SQL = @SQL + '[Help] [nvarchar](255) NULL default(''''), '
        SELECT @SQL = @SQL + 'CONSTRAINT [PK_' + @TableName + '] PRIMARY KEY CLUSTERED '
        SELECT @SQL = @SQL + '([ID] ASC '
        SELECT @SQL = @SQL + ')WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] '
        SELECT @SQL = @SQL + ') ON [PRIMARY]'

        BEGIN TRANSACTION
        EXEC(@SQL)
        COMMIT TRANSACTION                
    END
END;
GO

-- test case for function TableAdd
--EXEC TableAdd 'TestTable'
--EXEC TableAdd 'TestTable2'

Table Delete

/**** function TableDelete ****/

IF (OBJECT_ID('dbo.TableDelete') IS NOT NULL)
  DROP PROCEDURE dbo.TableDelete
GO

CREATE PROCEDURE dbo.TableDelete
(        
    @TableName [nvarchar](255)
) 
AS
BEGIN
    IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[' + @TableName + ']') AND type in (N'U'))
    BEGIN
        Declare @SQL varchar(1000)
        SELECT @SQL = 'DROP TABLE [dbo].[' + @TableName + ']'
        EXEC(@SQL)
    END
END;
GO

-- test case for function TableDelete
--EXEC TableDelete 'easyPlanAppOption'

Column Add

/**** function ColumnAdd ****/

IF (OBJECT_ID('dbo.ColumnAdd') IS NOT NULL)
  DROP PROCEDURE dbo.ColumnAdd
GO

CREATE PROCEDURE dbo.ColumnAdd
(        
    @TableName [nvarchar](255),
    @ColumnName [nvarchar](255),
    @DataType [nvarchar](50),
    @Size [nvarchar](5) = '',
    @Default [nvarchar](255) = '',
    @Nullable [nvarchar](10) = 'NULL' --  {NULL|NOT NULL} 
) 
AS
BEGIN
    IF NOT EXISTS(SELECT * FROM sys.columns 
                  WHERE Name = @ColumnName AND Object_ID = Object_ID(@TableName))
    BEGIN
        Declare @SQL varchar(1000)
        SELECT @SQL =   'ALTER TABLE ' + @TableName + ' ADD ' + @ColumnName + ' [' + @DataType + ']'
        IF @Size <> ''
        BEGIN
            SELECT @SQL = @SQL + '(' + @Size + ')'
        END
        SELECT @SQL = @SQL + ' ' + @Nullable + ' '
        IF @Default <> ''
        BEGIN
            IF @DataType = 'nvarchar'
            BEGIN
                SELECT @SQL = @SQL + ' default(''' + @Default + ''')'
            END
        END
        EXEC(@SQL)
    END
END;
GO

-- test case for function ColumnAdd
-- EXEC ColumnAdd 'easyPlanAppOption', 'test1', 'nvarchar', '30', 'hallo'        -- note: this can't remove by function ColumnRemove
-- EXEC ColumnAdd 'easyPlanAppOption', 'test1', 'nvarchar', '30'
-- EXEC ColumnAdd 'easyPlanAppOption', 'test2', 'int'

Column Remove

/*-----------------------------*/
/**** function ColumnRemove ****/

IF (OBJECT_ID('dbo.ColumnRemove') IS NOT NULL)
  DROP PROCEDURE dbo.ColumnRemove
GO

CREATE PROCEDURE dbo.ColumnRemove
(        
    @TableName [nvarchar](255),
    @ColumnName [nvarchar](255)
) 
AS
BEGIN
    IF EXISTS(SELECT * FROM sys.columns 
              WHERE Name = @ColumnName AND Object_ID = Object_ID(@TableName))
    BEGIN
        Declare @SQL varchar(1000)
        SELECT @SQL = 'ALTER TABLE ' + @TableName + ' DROP COLUMN ' + @ColumnName
        EXEC(@SQL)
    END
END;
GO

-- test case for function ColumnRemove
-- EXEC ColumnRemove 'easyPlanAppOption', 'test1'
-- EXEC ColumnRemove 'easyPlanAppOption', 'test1_rename'
-- EXEC ColumnRemove 'easyPlanAppOption', 'test2'

Column Rename

/**** function ColumnRename ****/

IF (OBJECT_ID('dbo.ColumnRename') IS NOT NULL)
  DROP PROCEDURE dbo.ColumnRename
GO

CREATE PROCEDURE dbo.ColumnRename
(        
    @TableName [nvarchar](255),
    @ColumnName [nvarchar](255),
    @ColumnNameNew [nvarchar](255)
) 
AS
BEGIN
    IF EXISTS(SELECT * FROM sys.columns 
              WHERE Name = @ColumnName AND Object_ID = Object_ID(@TableName))
    BEGIN
        Declare @Name [nvarchar](225)
        SELECT @Name = @TableName + '.' + @ColumnName
        EXEC sp_rename @Name, @ColumnNameNew, 'COLUMN'
    END
END;
GO

-- test case for function ColumnRename
-- EXEC ColumnRename 'easyPlanAppOption', 'test1', 'test1_rename'

Index Add

 /**** function IndexAdd ****/

IF (OBJECT_ID('dbo.IndexAdd') IS NOT NULL)
  DROP PROCEDURE dbo.IndexAdd
GO

CREATE PROCEDURE dbo.IndexAdd
(        
    @TableName [nvarchar](255),
    @ColumnName [nvarchar](255),
    @IndexName [nvarchar](255) = ''
) 
AS
BEGIN
    IF @IndexName = ''
    BEGIN
        SELECT @IndexName = 'idx' + @TableName + '_' + @ColumnName
    END
    IF NOT EXISTS(SELECT * FROM sys.indexes
              WHERE Name = @IndexName AND Object_ID = Object_ID(@TableName))
    BEGIN
        Declare @SQL varchar(1000)
        SELECT @SQL = 'CREATE INDEX ' + @IndexName + ' ON ' + @TableName + ' (' + @ColumnName + ')'
        EXEC(@SQL)
    END
END;
GO

-- test case for function IndexAdd
 --EXEC IndexAdd 'easyPlanAppOption', 'test1'
 --EXEC IndexAdd 'easyPlanAppOption', 'test2', 'index_TEST'

Index Remove

/**** function IndexRemove ****/

IF (OBJECT_ID('dbo.IndexRemove') IS NOT NULL)
  DROP PROCEDURE dbo.IndexRemove
GO

CREATE PROCEDURE dbo.IndexRemove
(        
    @TableName [nvarchar](255),
    @ColumnName [nvarchar](255),
    @IndexName [nvarchar](255) = ''
) 
AS
BEGIN
    IF @IndexName = ''
    BEGIN
        SELECT @IndexName = 'idx' + @TableName + '_' + @ColumnName
    END
    IF EXISTS(SELECT * FROM sys.indexes
              WHERE Name = @IndexName AND Object_ID = Object_ID(@TableName))
    BEGIN
        Declare @SQL varchar(1000)
        SELECT @SQL = 'DROP INDEX ' + @TableName + '.' + @IndexName
        EXEC(@SQL)
    END
END;
GO

-- test case for function IndexRemove
-- EXEC IndexRemove'easyPlanAppOption', 'test1'
-- EXEC IndexRemove 'easyPlanAppOption', 'test2', 'index_TEST'

Constraint Default Add

/*-------------------------------------*/
/**** function ConstraintDefaultAdd ****/

IF (OBJECT_ID('dbo.ConstraintDefaultAdd') IS NOT NULL)
  DROP PROCEDURE dbo.ConstraintDefaultAdd
GO

CREATE PROCEDURE dbo.ConstraintDefaultAdd
(        
    @TableName [nvarchar](255),
    @ColumnName [nvarchar](255),
    @ConstraintName [nvarchar](255),
    @Default [nvarchar](255)
) 
AS
BEGIN
    IF @ConstraintName = ''
    BEGIN
        SELECT @ConstraintName = 'DF_' + @TableName + '_' + @ColumnName
    END
    IF Object_ID(@ConstraintName, 'D') IS NULL                -- D is type of default constraint
    BEGIN
        IF NOT EXISTS(SELECT * FROM sys.tables 
                      WHERE Name = @TableName AND Object_ID = Object_ID(@TableName))
        PRINT('#Exception: table ' + @TableName + ' not exist')
        IF NOT EXISTS(SELECT * FROM sys.columns 
                      WHERE Name = @ColumnName AND Object_ID = Object_ID(@TableName))
        PRINT('#Exception: in table ' + @TableName  +' column ' + @ColumnName + ' not exist')            
        Declare @SQL varchar(1000)
        IF @Default = ''
            SELECT @SQL = 'ALTER TABLE [dbo].['+ @TableName + '] ADD CONSTRAINT ['+ @ConstraintName + '] DEFAULT (' + char(39) + char(39) + ') FOR [' + @ColumnName + ']';
        ELSE
            SELECT @SQL = 'ALTER TABLE [dbo].['+ @TableName + '] ADD CONSTRAINT ['+ @ConstraintName + '] DEFAULT (' + @Default + ') FOR [' + @ColumnName + ']';
        EXEC(@SQL)
    END
END;
GO

-- test case for function ConstraintDefaultAdd
-- EXEC ColumnAdd 'easyPlanAppOption', 'test2'
-- EXEC ConstraintDefaultAdd 'easyPlanAppOption', 'test2', '', '0'
-- EXEC ColumnRemove 'easyPlanAppOption', 'test2'

Constraint Default Remove

/*----------------------------------------*/
/**** function ConstraintDefaultRemove ****/

IF (OBJECT_ID('dbo.ConstraintDefaultRemove') IS NOT NULL)
  DROP PROCEDURE dbo.ConstraintDefaultRemove
GO

CREATE PROCEDURE dbo.ConstraintDefaultRemove
(        
    @TableName [nvarchar](255),
    @ColumnName [nvarchar](255),
    @ConstraintName [nvarchar](255) = ''
) 
AS
BEGIN
    IF @ConstraintName = ''
    BEGIN
        SELECT @ConstraintName = 'DF_' + @TableName + '_' + @ColumnName
    END
    IF Object_ID(@ConstraintName, 'D') IS NOT NULL                -- D is type of default constraint
    BEGIN
        Declare @Name [nvarchar](225)
        SELECT @Name = '[dbo].[' + @TableName + ']'
        Declare @SQL varchar(1000)
        SELECT @SQL = 'ALTER TABLE '+ @TableName + ' DROP ['+ @ConstraintName + ']'
        EXEC(@SQL)
    END
END;
GO

-- test case for function ConstraintDefaultRemove
-- EXEC ColumnAdd 'easyPlanAppOption', 'test2'
-- EXEC ConstraintDefaultRemove 'easyPlanAppOption', 'test2'
-- EXEC ColumnRemove 'easyPlanAppOption', 'test2'

Constraint Default Rename

 /**** function ConstraintDefaultRename ****/

IF (OBJECT_ID('dbo.ConstraintDefaultRename') IS NOT NULL)
  DROP PROCEDURE dbo.ConstraintDefaultRename
GO

CREATE PROCEDURE dbo.ConstraintDefaultRename
(        
    @TableName [nvarchar](255),
    @ConstraintName [nvarchar](255),
    @ConstraintNameNew [nvarchar](255)
) 
AS
BEGIN
    IF Object_ID(@ConstraintName, 'D') IS NOT NULL                -- D is type of default constraint
    BEGIN
        Declare @Name [nvarchar](225)
        SELECT @Name = @TableName + '.' + @ConstraintName
        EXEC sp_rename @Name, @ConstraintNameNew, 'OBJECT'
    END
END;
GO

-- test case for function ConstraintDefaultRename
-- EXEC ConstraintDefaultRename 'easyPlanAppOption', 'test2', 'index_TEST'

developed by SOWI computer science

Send comments on this topic to app@sowi.ch (support and feedback)