Tags: database, ensure, fellows, field, identity, microsoft, mysql, oracle, replication, scriptupdate, server, sql, syscolumns, tables, update

Update SysColumns !?

On Database » Microsoft SQL Server

1,388 words with 4 Comments; publish: Tue, 04 Dec 2007 19:21:00 GMT; (25046.88, « »)

Hey fellows,

I want to update all tables in my database to ensure that the identity field is set to NOT FOR REPLICATION.

This is my script:

UPDATE SysColumns

SET ColStat = 9

WHERE ID IN ( SELECT ID

FROM SysColumns INNER JOIN SysObjects ON

SysObjects.ID = SysColumns.ID )

WHERE SysObjects.xtype = 'U' -- UserTable

AND SysColumn.ColStat = 1

)

Does anyone have some experience with this kind of queries?

Greetz Da Witte

All Comments

Leave a comment...

  • 4 Comments
    • I don't think I'd mess with the system tables...

      Look up ALTER TABLE...

      #1; Tue, 11 Dec 2007 16:13:00 GMT
    • === Original Words ===

      Brett Kaiser

      I don't think I'd mess with the system tables...

      Look up ALTER TABLE...

      suggestions ??

      I've tried something but not succesfully

      #2; Tue, 11 Dec 2007 16:14:00 GMT
    • sp_configure 'allow', 1

      reconfigure with override

      go

      UPDATE SysColumns

      SET ColStat = 9

      WHERE SysColumns.ID IN ( SELECT SysColumns.ID

      FROM SysColumns INNER JOIN SysObjects ON

      SysObjects.ID = SysColumns.ID

      WHERE SysObjects.xtype = 'U' -- UserTable

      AND SysColumns.ColStat = 1

      )

      go

      sp_configure 'allow', 0

      reconfigure with override

      go

      #3; Tue, 11 Dec 2007 16:15:00 GMT
    • Thankx !! it works perfectly now.
      #4; Tue, 11 Dec 2007 16:16:00 GMT