Amazon.com Widgets All posts tagged 'T-SQL'

WilliaBlog.Net

I dream in code

About the author

Robert Williams is an internet application developer for the Salem Web Network.
E-mail me Send mail
Code Project Associate Logo
Go Daddy Deal of the Week: 30% off your order at GoDaddy.com! Offer expires 11/6/12

Recent comments

Archive

Authors

Tags

Disclaimer

The opinions expressed herein are my own personal opinions and do not represent my employer's view in anyway.


How to update multiple tables using T-SQL

Lets say you have a database made up of many tables. All of those tables have a field called "DateCreated" which cannot be null, but at the time the tables were created, you didn't think to set a default value for the field. Now you could open each table in design mode and set the default value manually, but here is an easier way:

The syntax to set a default value looks like this:

ALTER TABLE [table-name]
ADD CONSTRAINT constraint-name DEFAULT default-value FOR column-NAME;

While quicker that opening each table in design view this still only allows you to set the default value for a single table at a time. However, we can write some simple SQL that will generate a complete SQL Script for us:

SELECT 'ALTER TABLE [' + sysobjects.NAME +
'] ADD CONSTRAINT DF_' + sysobjects.NAME + '_DateCreated DEFAULT getdate()
FOR DateCreated;' from sysobjects inner join syscolumns on
sysobjects.id = syscolumns.id
inner join systypes on
syscolumns.xtype = systypes.xtype
WHERE syscolumns.NAME = 'DateCreated'

Run that query and each row returned will contain an Alter Table statement for each table. Simply highlight all the rows returned, then copy and paste them to a new query and run it. The SysObjects table contains one row for each object (table, column, constraint, default, log, rule, stored procedure, and so on) created within a database. This means you could write a query to update the sysobjects table directly and make this even easier, but if you mess up the sysobjects table your database could be trashed so use caution if you go that route.


Categories: SQL Server
Posted by Williarob on Friday, February 19, 2010 1:35 PM
Permalink | Comments (0) | Post RSSRSS comment feed