Search code examples
visual-studio-2013defaultdatabase-project

Mass change column default values in database project

I have a Visual Studio 2013 database project, in which I would like to change all GUID columns with a default of newid() to newsequentialid().

Using the following query to identify the columns:

SELECT so.name AS table_name, 
    sc.name AS column_name, 
    sm.text AS default_value
FROM sys.sysobjects so
JOIN sys.syscolumns sc ON sc.id = so.id
LEFT JOIN sys.syscomments SM ON sm.id = sc.cdefault
WHERE so.xtype = 'U'
    AND sm.text = '(newid())'
ORDER BY so.[name], sc.colid

there are a total of 62 columns in 62 tables.

Is there a way to do this in Visual Studio other than going to each table definition one-by-one and changing the default value?

Solution

  • Find and replace in all *.sql files in the project would work to do that in the project. I'd try the find/replace first and then maybe see if you need to tweak the refactorlog in some fashion to handle refactoring.