SQL Server: dynamic RowNumber update of a table

Question

I m trying to update all rows of my database with a RowNumber ...

In my loop running through my tables I got these two dynamic statements:

Set @SqlStringOne = ('
            Alter Table ' +@Schema+'.'+@Table+'
            Add RowID int;
            ')
    Exec(@SqlStringOne); 

(is working and adding a new RowID (RowNum) Column into my table) and

Set @SqlStringTwo = ('
            update ' +@Schema+'.'+@Table+'
            Set  x.RowID = x.RowID_x
            from
            (select 
             RowID,
             ROW_NUMBER() OVER 
            (ORDER BY (Select TOP 1 COLUMN_NAME from INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE where TABLE_NAME = '+@Table+')) as RowID_x
            from '+@Schema+'.'+@Table+') x
            ');
    Exec dbo.sp_executesql @stmt = @SqlStringTwo

But the update is not working ...

Thanks in advance

Answer 1

I don't understand the order by. Why not just do this?

update x
    set x.RowID = x.RowID_x
    from (select  t.*,
                  row_number() over (order by (select null)) as rowid_x
          from ' + @Schema + '.' + @Table + '
         ) x;

You can use x in the update. I don't understand the reference to the system table for the order by. It should be returning a constant, so you might as well use (select null).