Query Advise...

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » T-SQL (SS2K8) » Query Advise...

Query Advise...

Rate Topic

Display Mode

Topic Options

Author

Message

tooba111

Posted Sunday, February 17, 2013 9:31 PM

SSC Rookie

Group: General Forum Members
Last Login: Friday, February 22, 2013 9:10 PM
Points: 25, Visits: 112

Hi guys,

I am not sure it is possible or not but would like to ask here.

I want know is anyone know any TRICK/Query that search through all tables in the database and IF column is there that's fine otherwise create a new column.

Please let me know if it is make sense?

Post #1421027

joeroshan

Posted Sunday, February 17, 2013 9:44 PM

Mr or Mrs. 500

Group: General Forum Members
Last Login: Thursday, May 16, 2013 3:45 AM
Points: 572, Visits: 1,157

There is not a single statement like that, but it is possible to generate athe alter statement by querying sys.columns and sys.tables.

Make sure you verify the table and schema name before you execute any such queries across the database, or you may be breaking someone else applications

-- Roshan Joe
*******************************************
Jeff Moden -Forum Etiquette: How to post data/code on a forum to get the best help
Custom cleanup script for backups

Post #1421028

tooba111

Posted Sunday, February 17, 2013 10:01 PM

SSC Rookie

Group: General Forum Members
Last Login: Friday, February 22, 2013 9:10 PM
Points: 25, Visits: 112

Thanks for reply and telling me yes there is a way. Is it possible can you help me to with Query?
and what you mean by make sure about Table and Column?

Post #1421030

joeroshan

Posted Monday, February 18, 2013 2:06 AM

Mr or Mrs. 500

Group: General Forum Members
Last Login: Thursday, May 16, 2013 3:45 AM
Points: 572, Visits: 1,157

For eg if your column name is CreateUser, the below query will give you all the tables wiothout the column

Select S.name,T.name
from sys.tables T join Sys.schemas S
on T.schema_id=S.schema_id
where T.object_id not in
(Select object_id from sys.columns where name='CreateUser')

If you are ok with the list

Folowing query will generate the alter script that you can paste in new window and run

Select 'Alter table '+S.name+'.'+T.name + ' Add CreatUser varchar(255) null'
from sys.tables T join Sys.schemas S
on T.schema_id=S.schema_id
where T.object_id not in
(Select object_id from sys.columns where name='CreateUser')

Make sure you change the datatype nullability etc to your requirement

-- Roshan Joe
*******************************************
Jeff Moden -Forum Etiquette: How to post data/code on a forum to get the best help
Custom cleanup script for backups

Post #1421093

« Prev Topic | Next Topic »

Permissions