Alter all tables in SQL

As I wanted to move an on-premise MS SQL database to Azure SQL I was notified with an error message telling me this:

Error SQL71564: Element Column: [dbo].[SomeTable].[Id] has an unsupported property IsRowGuidColumn set and is not supported when used as part of a data package.

Every table in the database has a GUID (uniqueidentifier) as a primary key and apparently, the ROWGUID is set to YES. It’s too bad there isn’t much documentation on the ROWGUID option telling us what it actually does. By searching for this feature I have discovered it is meant to create a unique key across multiple servers, only useful if you want to enable the SQL Server Replication feature. I’m not really sure about this description, as a uniqueidentifier (GUID) should be unique no matter what. However, this post is not about GUIDs in SQL.

Because of the above error, I had to change all tables in the database. The ROWGUID had to be removed. This can be done manually of course, but that’s not really fun.

I decided to generate a query which creates an ALTER script for me. The script will iterate through all tables and SELECT the query which needs to be run. All which is left to do is copy the result to a query window and run it.

SELECT 'ALTER TABLE TheDatabaseName.dbo.[' + name + '] ALTER COLUMN Id DROP ROWGUIDCOL'  AS CommandText
FROM (SELECT name
    FROM dbo.sysobjects
    WHERE type = 'U') as usertables

You can of course create your own script in the SELECT statement. This is all up to you.


Share

comments powered by Disqus