2 Comments
Here is a tip I discovered today while at work. I had earlier postponed the addition of common columns needed for all the tables (columns needed for auditing purposes like AddedDate, AddedBy etc.). But, the time had come now to type in and set the properties for columns on all tables one by one. I finished adding these columns to the first table. When on the second table, my laziness kicked in and wanted an easier way to do this mundane task. So,
  1. Opened the design of table1 using SQL Server Management Studio.
  2. Selected the last 4 columns.
  3. Ctrl+C.
  4. Opened the design view of table2
  5. Ctrl+V
And there it was, all the columns along with the properties replicated in a jiffy!

Comments

Comment by Raghuram (AJ)

Mani, that's one good GUI way of doing it. But isn't this repetitive too? How about writing a small script to generate the scripts that you need to run to add columns to all the tables. Here's a sample:

I want to add 2 columns to all tables in the database:

SELECT
'ALTER TABLE ' + TABLE_SCHEMA + '.' + TABLE_NAME
+ ' ADD Col1 INT NULL, Col2 INT NULL'
FROM
INFORMATION_SCHEMA.TABLES;

You just execute the result set and you have the new columns in about jiffy*10! (Btw, did you know jiffy is 1/100th of a second?)

Comment by Manimaran

Oh, yeah. never underestimate the power of scripting. I should have really done this via the script you've provided. Will remember that anyway.