February 17, 2013 at 9:31 pm
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?
February 17, 2013 at 9:45 pm
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
February 17, 2013 at 10:01 pm
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?
February 18, 2013 at 2:06 am
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
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply