Add columns to tables where the columns are not preswent

Question

Post reply

Add columns to tables where the columns are not preswent

Cindy Miller

SSC Enthusiast

Points: 149
More actions
August 9, 2005 at 4:12 pm

#87089

Hi,
I'm looking for the components of a query where I can add 2 columns to all the tables in the database where the columns are not already present. I think that I need a 'select' to find where 'x' and 'y' are not present and then an Add (x, uniqueidentifier (10)) to add the columns to the tables that were selected in the query. The problem is the select - select what from where to get all the tables in the db?
Thanks!

Viewing 13 posts - 1 through 13 (of 13 total)

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

sushila SSC-Dedicated Points: 35293 More actions · Answer 1

If I understand you correctly, I think this should do it...unfortunately I cannot test this before posting...

select name from sysobjects

where name not in

(select so.name as name

from sysobjects so

inner join

syscolumns sc

on

so.id = sc.id

and sc.name in ('x', 'y'))

and sysobjects.xtype = 'U'

order by name

**ASCII stupid question, get a stupid ANSI !!!**

Josh-242404 Grasshopper Points: 15 More actions · Answer 2

Here is a really nasty way of doing it. Basically you can change col1 and col2 to be the names of the columns you need to add, then at the bottom of the script you need to change the datatypes and defaults to whatever you are needing.

Hope this helps.

USE PerfTune
GO

DECLARE @sql VARCHAR(8000)
DECLARE @tablename VARCHAR(255)
DECLARE @col1 VARCHAR(255)
DECLARE @col2 VARCHAR(255)
SET @col1='t_id'
SET @col2='q_id'

DECLARE curTables CURSOR FOR 
 SELECT DISTINCT TABLE_NAME
 FROM INFORMATION_SCHEMA.Tables T
 WHERE TABLE_TYPE='BASE TABLE'
  AND TABLE_NAME NOT IN (SELECT DISTINCT C1.TABLE_NAME 
    FROM INFORMATION_SCHEMA.Columns C1
    INNER JOIN INFORMATION_SCHEMA.Columns C2
    ON C1.TABLE_NAME = C2.TABLE_NAME
    INNER JOIN INFORMATION_SCHEMA.Tables T
    ON C1.TABLE_NAME = T.TABLE_NAME
    WHERE TABLE_TYPE='BASE TABLE'
     AND C1.COLUMN_NAME = @col1
     AND C2.COLUMN_NAME = @col2)
OPEN curTables

FETCH NEXT FROM curTables INTO @tablename

WHILE @@FETCH_STATUS = 0
BEGIN
 SET @sql = '
 IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = '''+@tablename+''' 
 AND COLUMN_NAME = '''+@col1+''')
 BEGIN TRAN ['+@tablename+']
 G'+'O
 IF @@TRANCOUNT > 0
 BEGIN
  EXEC(''ALTER TABLE [dbo].['+@tablename+'] ADD 
   ['+@col1+'] [INT] NOT NULL
    CONSTRAINT <A href="mailtoF_'+@tablename+'_'+@col1+'">DF_'+@tablename+'_'+@col1+' DEFAULT 0'')
  COMMIT TRAN
 END
 G'+'O
 IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = '''+@tablename+''' 
 AND COLUMN_NAME = '''+@col2+''')
 BEGIN TRAN ['+@tablename+']
 G'+'O
 IF @@TRANCOUNT > 0
 BEGIN
  EXEC(''ALTER TABLE [dbo].['+@tablename+'] ADD 
   ['+@col2+'] [INT] NOT NULL
    CONSTRAINT <A href="mailtoF_'+@tablename+'_'+@col2+'">DF_'+@tablename+'_'+@col2+' DEFAULT 0'')
  COMMIT TRAN
 END
 G'+'O
'

 -- PRINT @sql
 EXEC(@sql)
 FETCH NEXT FROM curTables INTO @tablename
END

CLOSE curTables
DEALLOCATE curTables
GO

Cindy Miller SSC Enthusiast Points: 149 More actions · Answer 3

Cindy Miller

SSC Enthusiast

Points: 149

August 10, 2005 at 8:16 am

#581046

Josh

This scared me but I appreciate your help!

Cindy Miller SSC Enthusiast Points: 149 More actions · Answer 4

Sushila,

Thanks so much - worked like a charm! A nice list of tables to insert the columns - thanks again!

sushila SSC-Dedicated Points: 35293 More actions · Answer 5

glad it worked!

**ASCII stupid question, get a stupid ANSI !!!**

Cindy Miller SSC Enthusiast Points: 149 More actions · Answer 6

I added LastModDateTime and LastModUser to every table.

noeld SSC Guru Points: 96590 More actions · Answer 7

>> ...

-- better reports, less overhead, meets legal requierments and all the other things this design flaw does not. <<

Yes and you for got to metion a ton of $$$ for something easily maintainable for free

* Noel

sushila SSC-Dedicated Points: 35293 More actions · Answer 8

thank you for throwing that in noel...otherwise I'd have thought that it's only my clients who're tightfisted (& unreasonable) enough to want much more bang for their meager bucks!

**ASCII stupid question, get a stupid ANSI !!!**

Cindy Miller SSC Enthusiast Points: 149 More actions · Answer 9

Thanks Noel for responding. I am new to this forum and wasn't sure how to respond. I have seen lastmod in lots of apps and couldn't believe that 'we' had built so much garbage

noeld SSC Guru Points: 96590 More actions · Answer 10

as of last year Lumingent Entegra was $10,000.00 per server plus $2000.00 maintenance fee and all that was just software then you need to add your collection server and your reporting server.

If you put together the licensing requiered for all that "extra" infrastructure you are going to arrive at a not very pretty picture for companies that are not very big. Only BIG guys can afford such compromises

Just my $0.02 ... you see I am not expensive either

* Noel

sushila SSC-Dedicated Points: 35293 More actions · Answer 11

you see I am not expensive either......

you certainly aren't noel - though if you were a piece of software you should be.......for all the knowledge and expertise you pack in your gray cells!!!

..thank you for being the ubiquitous friendly ghost who has a reponse for any post under any forum - and always with a smile!

**ASCII stupid question, get a stupid ANSI !!!**

noeld SSC Guru Points: 96590 More actions · Answer 12

Thanks! you are very kind

BTW Did you see my reply to your trigger question ?

* Noel