information_schema.columns

[DLu]

Hi:

I need to change a column default to '' and not null for 1500 databases accross 10 servers.

if exists (select * from information_schema.columns
where table_name = 'tblABC'
and column_name = 'columnX'
and data_type = 'VARCHAR'
and is_nullable = 'No'
and column_default = '('')'
)
begin
--do something to implement
end

Here, there is a problem for and column_default = '('')'
I have tried ''''+'('')' + '''' or "'('')'", neither works.
Do you have any idea to deal with the ('') ?

thanks
-D

[MAK]

use tempdb
go
create table tblABC(column1 int, columnX varchar(100) NOT NULL default "('')")
go
set quoted_identifier off
go
select * from information_schema.columns
where table_name = "tblABC"
and column_name = "columnX"
and data_type = "VARCHAR"
and is_nullable = "No"
and column_default = "('('''')')"

[DLu]

Hi MAK:
It works.
I just need to run the set quoted_identifier off first.

thanks for the help.
-D

[DLu]

MAK:

This is derived question about information_schema.

When I need to check a internal service pack is successfully installed or not, I could use information_schema for all changes with table, coulmn, datatype, size, nullable, default, fk constraint and other constraints.

But how could i check triggers and proc which is not provided via information_schema? I still have to go through sysobjects?

Worse, if it is with encryption option, it looks the crdate is the only indicator about when it is installed, might not be enough to me to verify it is ok. I hope there is a total size of bytes as another indicator for comparision.

thanks
-D