Hello
I have created this code to go through all the tables in my databases,
changing Nullable fields to 'Not null'
-- Drop Procedure DropNulls
-- use ChangeOrders_BESQLSERVERSQL
--Create procedure DropNulls as

set xact_abort OFF
--select 1/0
Declare MyTablesCur Cursor for SELECT [name]  FROM
[ChangeOrders_BESQLSERVERSQL].[dbo].[sysobjects] where xtype ='U'
and patindex('%[_]%',Name) =0
Declare @TableName varchar(30)
Declare @SQLToRUn Varchar(250)
declare @NotChanged int
Open MyTablesCur
Fetch next From MyTablesCur into @TableName
print @TableName
while ( @@fetch_status = 0 )
    begin
            /*Declare @SQLToRUn Varchar(250)
            Declare @TableName varchar(30)
            select @TableName = 'tblDataValidationReportsChas' */
            declare MyColsCUR insensitive cursor for
SELECT COLUMN_NAME,DATA_TYPE,isnull(CHARACTER_MAXIMUM_LENGTH,0) AS
'LENGTH'

FROM  SYSCOLUMNS,(SELECT COLUMN_NAME,
IS_NULLABLE,
DATA_TYPE,
CHARACTER_MAXIMUM_LENGTH,
Ordinal_Position,
COLUMNPROPERTY(OBJECT_ID('#TABLE_NAME#'),
               INFORMATION_SCHEMA.COLUMNS.COLUMN_NAME,
               'IsIdentity') AS IsIdentity,
COLUMNPROPERTY(OBJECT_ID('#TABLE_NAME#'),
               INFORMATION_SCHEMA.COLUMNS.COLUMN_NAME,
               'IsRowGuidCol') AS IsRowGuidCol

FROM
INFORMATION_SCHEMA.COLUMNS

WHERE
TABLE_NAME = @TableName and is_nullable = 'YES') AS A

WHERE
ID
IN
(SELECT ID FROM SYSOBJECTS WHERE TYPE='U' AND NAME =@TableName)
AND
A.COLUMN_NAME =NAME

Order By
Ordinal_Position



--select name     from syscolumns where id = object_id( @TableName  )
and isnullable<>0
--            select name     from syscolumns where id = object_id(
'tblDataValidationReportsChas'  ) and isnullable<>0
            open MyColsCUR
        Declare @COLUMN_NAME Varchar(60), @DATA_TYPE varchar(10),@LENGTH int

            declare @ColName varchar(30)
            fetch next from MyColsCUR into @COLUMN_NAME , @DATA_TYPE ,@LENGTH
            while ( @@fetch_status = 0 )
            begin
            --print @tableName + ' ' +  @COLUMN_NAME
                select @SQLTorun = 'Alter Table ' + @TableNAme +' Alter Column ' +
@COLUMN_NAME + ' ' + @Data_Type
                if @Length!= 0
                    begin
                        select @SQLTorun = @SQLTorun + '(' + Rtrim(cast(@Length as
Varchar(10))) + ')'
                    end
                select @SQLTorun = @SQLTorun +  ' Not Null'
                print '@SQLTorun:' + @SQLToRUn
                exec (@sqltorun)

                --exec ('Alter Table tblDataValidationReportsChas Alter Column
strReportGroup nvarchar Not Null')
                --Select @@Error
                Select @notchanged  = @@Error
                print 'Error ' + cast(@NotChanged as varchar(100))
                --break
            fetch next from MyColsCUR into @COLUMN_NAME , @DATA_TYPE ,@LENGTH

            end
            close MyColsCUR
            deallocate MyColsCUR
            if @SQLTORun!= ''
                break
            Fetch next From MyTablesCur into @TableName
            print @TableName
    end
close MyTablesCur
deallocate MyTablesCur
set xact_abort On
--sp_helpindex tblDataValidationReportsChas
--exec dropnulls

The code works util in encounters for example a column which has an
index which references it: then it fails with
Server: Msg 5074, Level 16, State 8, Line 1
The index 'ButtonName' is dependent on column 'ButtonName'.
Server: Msg 4922, Level 16, State 1, Line 1
ALTER TABLE ALTER COLUMN ButtonName failed because one or more objects
access this column.


I have  questions
1) How I could make the batch continue with the remaining
columns/tables after if fails? When it fails, it doesn't even execute
the lines
                Select @notchanged  = @@Error
                print 'Error ' + cast(@NotChanged as varchar(100))
Why not?

2) How could I write code to find the dependencies, drop them do the
Alter Column and then re-establish them?
Thanking you in advance

Top 100 ... Order by in views SQL 2005 problem
Passing parameter from a recordset in a Stored Procedure
To write SPs, or not to write SPs, that is the question....
Help with Subquery and "Group By"
cumulative problem