Newbie question - loop thru all tables and create columns

Question

Newbie question - loop thru all tables and create columns

jabo73

Old Hand

Points: 376
More actions
January 30, 2007 at 7:34 pm

#69511

I need to loop thru all non-system tables in my database and add a standard set of columns (i.e. DateCreated, EmpCreated, DateUpdated, EmpUpdated, ConcurrencyTimeStamp).
I don't want to go thru each table and manually create these as that would be a real pain in the butt. Hence my question. How can I can loop thru all non-system tables and add these columns? Thanks for your help!

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

K. Brian Kelley SSC Guru Points: 114642 More actions · Answer 1

The easiest way is to build a script to write the script for you. You can simply query the schema and table name from INFORMATION_SCHEMA.TABLES and then built your ALTER TABLE statements as a result set. Then copy that result set and verify and execute. Something like...

SELECT 'ALTER TABLE [' + TABLE_SCHEMA + '].[' + TABLE_NAME + ']
... rest of your ALTER TABLE code ...
GO
'
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'

K. Brian Kelley
@kbriankelley

jabo73 Old Hand Points: 376 More actions · Answer 2

that's exactly what I needed. Worked great thanks! Just in case anyone else wants to use it, don't include the GO keyword and also watch out for the resultset containing a row for the sysdiagrams table. But other than that this was a great solution, thanks again!

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 3

Ya anytime you build something like this make sure you manually run it once, then after all the bugs are gone you can set up an sp with dynamic sql to do the job for you.

Rule #1 Backup first.

#2 Test on a testdb on a test server

#3 You'll never have to say oops, what the heck happened there .

K. Brian Kelley SSC Guru Points: 114642 More actions · Answer 4

The GO is a batch separator for clients like Query Analyzer. I tend to add them in when I'm scripting these sorts of things to run manually. That way it's easier to see the break in code steps.

K. Brian Kelley
@kbriankelley

Scott Coleman One Orange Chip Points: 27452 More actions · Answer 5

I do something similar to add a standard set of columns to selected tables, but in our case some of the columns may already exist. This query will only add the missing columns, and it creates standard constraint names.

SELECT

'ALTER TABLE ' + QUOTENAME(t.TABLE_SCHEMA) + '.' + QUOTENAME(t.TABLE_NAME)

+ ' ADD ' + n.column_name + n.column_type + REPLACE(n.default_constraint, '<tbl>', t.TABLE_NAME) + '

' + 'GO'

FROM (

SELECT 'Groucho' AS column_name, ' int not null' AS column_type, '' AS default_constraint

UNION ALL SELECT 'Chico' AS column_name, ' varchar(100)' AS column_type, '' AS default_constraint

UNION ALL SELECT 'Harpo' AS column_name, ' varchar(50)' AS column_type, '' AS default_constraint

UNION ALL SELECT 'Zeppo' AS column_name, ' smallint' AS column_type, '' AS default_constraint

UNION ALL SELECT 'GummoFlag' AS column_name, ' bit' AS column_type, ' CONSTRAINT [DF_<tbl>_GummoFlag] DEFAULT (0)' AS default_constraint

) AS n

CROSS JOIN INFORMATION_SCHEMA.TABLES AS t

LEFT JOIN INFORMATION_SCHEMA.COLUMNS AS c ON t.TABLE_NAME = c.TABLE_NAME AND n.column_name = c.COLUMN_NAME

WHERE (t.TABLE_NAME <> 'dtproperties') AND (t.TABLE_TYPE = 'BASE TABLE') AND (c.TABLE_NAME IS NULL)