Author |
Topic |
mohan123
Constraint Violating Yak Guru
252 Posts |
Posted - 2013-03-19 : 04:11:30
|
hello all,i have two column created and modified columns in all tables in database.Almost these columns will come in all tables in entire database.So is there any way to make null these columns in one go like 300 tables. Suggest me .....P.V.P.MOhan |
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2013-03-19 : 04:32:53
|
If all tables have created and modified columns, then use the follwoing sql code:exec sp_MSforeachtable 'ALTER TABLE ? ALTER Column created DATETIME NULL'GOexec sp_MSforeachtable 'ALTER TABLE ? ALTER COLUMN modified DATETIME NULL'NOTE: sp_MSforeachtable is undocumented procedure.So do not use in production server--Chandu |
|
|
mohan123
Constraint Violating Yak Guru
252 Posts |
Posted - 2013-03-19 : 04:51:41
|
while executing the above query if the table have created column it is making null but if the table doesn't have created column it is showing error like ALTER TABLE ALTER COLUMN failed because column 'Modified' does not exist in tablehow to overcome this oneP.V.P.MOhan |
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2013-03-19 : 05:24:41
|
Try this once........exec sp_MSforeachtable 'IF EXISTS ( SELECT 1 FROM sys.columns WHERE table_name = ? and column_name = ''created'' ) ALTER TABLE ? ALTER Column created DATETIME NULL'exec sp_MSforeachtable 'IF EXISTS ( SELECT 1 FROM sys.columns WHERE table_name = ? and column_name = ''modified'' ) ALTER TABLE ? ALTER Column created DATETIME NULL' --Chandu |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-03-19 : 10:17:25
|
it should beexec sp_MSforeachtable 'IF EXISTS ( SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE ''['' + table_Schema + ''].['' + table_name + '']'' = ''?'' and column_name = ''created'' ) ALTER TABLE ? ALTER Column created DATETIME NULL'exec sp_MSforeachtable 'IF EXISTS ( SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE ''['' + table_Schema + ''].['' + table_name + '']'' = ''?'' and column_name = ''modified'' ) ALTER TABLE ? ALTER Column modified DATETIME NULL' ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-03-19 : 10:19:34
|
quote: Originally posted by bandi Try this once........exec sp_MSforeachtable 'IF EXISTS ( SELECT 1 FROM sys.columns WHERE table_name = ? and column_name = ''created'' ) ALTER TABLE ? ALTER Column created DATETIME NULL'exec sp_MSforeachtable 'IF EXISTS ( SELECT 1 FROM sys.columns WHERE table_name = ? and column_name = ''modified'' ) ALTER TABLE ? ALTER Column created DATETIME NULL' --Chandu
wont work like above1. ? returns fully qualified tablename ie [schema].[tablename]2. sys.columns doesnt have table_name column so should be using INFORMATION_SCHEMA.COLUMNS table instead3. for using table name as string use '' around ?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2013-03-20 : 00:40:05
|
Yes... that is my mistake... I haven't checked above query with sys.columns....--Chandu |
|
|
mohan123
Constraint Violating Yak Guru
252 Posts |
Posted - 2013-03-20 : 00:43:54
|
yeah thanks...my work got over in first question itself ALTER table name check check constraint ALL this query done trick for making all noCHECK constraint to Check constraint ....thanks chandu and visakhP.V.P.MOhan |
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2013-03-20 : 00:51:54
|
quote: Originally posted by mohan123 yeah thanks...my work got over in first question itself ALTER table name check check constraint ALL this query done trick for making all noCHECK constraint to Check constraint ....thanks chandu and visakhP.V.P.MOhan
Welcome...--Chandu |
|
|
|