I was working on a project last week where I needed to update the columns and data types in tables in SQL Server. It is pretty quick to do this manually such as:
ALTER TABLE orders
ALTER COLUMN orderid varchar(36) NOT NULL
I prefer this compared to the code SQL Server generates in the change script which typically renames the existing table, creates a new table, copies the data from the previous table to the new table, and then restores indices and keys. While that is appropriate for dramatic changes, it is overkill for something of this nature.
Since I love using tools and scripting, I decided to make a small Python program which would let me just specify which tables and columns to modify. This simplifies maintenance as I only have to manage the tables and columns rather than reading the entire SQL script looking for missing columns or keys.
While I was developing the Python script, I wanted to add enough SQL to avoid obvious errors such as trying to add duplicate primary keys or trying to alter columns that are already NOT NULL. I used MSDN and looked at some of the generated SQL to get the following templates:
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID('orders') AND COLUMNPROPERTY(id, 'orderid', 'AllowsNull') = 1)
BEGIN
PRINT 'Altering column: orders.orderid'
ALTER TABLE orders
ALTER COLUMN orderid varchar(36) NOT NULL
END
GO
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID('orders') AND OBJECTPROPERTY(id, 'TableHasPrimaryKey') = 0)
BEGIN
PRINT 'Adding primary key: orders.orderid'
ALTER TABLE orders
ADD CONSTRAINT PK_orders
PRIMARY KEY (orderid)
END
GO
So the Python script used the string templates to write the output SQL and it looked like:
#!/usr/bin/env python
"""Generate a T-SQL script to set the appropriate NOT NULL
columns and PRIMARY KEY indices for the database.
TODO: Create indices for specific columns as well
"""
import string
# database properties
tables = {
"orders": { 'notnull': [('orderid', 'varchar(36)')],
'pk' : ['orderid'] },
"accounts": { 'notnull': [('accountid', 'varchar(50)'),
('name', 'varchar(100)')],
'pk' : ['accountid'] },
# More tables and columns...
}
# Functions
def generatenotnull(table, column, datatype):
print alternotnull % { 'table':table, 'column':column, 'datatype':datatype }
def generatepk(table, columns):
print alterpk % { 'table':table, 'columns':string.join(columns, '"') }
# SQL statements and formats
alternotnull = """IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID('%(table)s') AND COLUMNPROPERTY(id, '%(column)s', 'AllowsNull') = 1)
BEGIN
PRINT 'Altering nullable column: %(table)s.%(column)s'
ALTER TABLE %(table)s
ALTER COLUMN %(column)s %(datatype)s NOT NULL
END
GO"""
alterpk = """IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID('%(table)s') AND OBJECTPROPERTY(id, 'TableHasPrimaryKey') = 0)
BEGIN
PRINT 'Adding primary key: %(table)s.%(columns)s'
ALTER TABLE %(table)s
ADD CONSTRAINT PK_%(table)s
PRIMARY KEY (%(columns)s)
END
GO"""
# Main entry
if __name__ == "__main__":
for table in tables:
print "/* ==== Processing %s ==== */" % table
tabledict = tables[table]
for column in tabledict['notnull']:
generatenotnull(table, column[0], column[1])
# TODO: Allow for compound primary keys
generatepk(table, tabledict['pk'])
print ""
Comments are closed.