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 .....
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 table
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'
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 above
1. ? returns fully qualified tablename ie [schema].[tablename] 2. sys.columns doesnt have table_name column so should be using INFORMATION_SCHEMA.COLUMNS table instead 3. for using table name as string use '' around ?
------------------------------------------------------------------------------------------------------ SQL Server MVP http://visakhm.blogspot.com/
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 visakh P.V.P.MOhan
how to make a particular column null in database
Posted - 03/19/2013 : 04:11:30
