SQL Server – Enable Toolbox Window in SQL Server Management Studio

SQL Server Management Objects SMO is your answer. You can use it to accomplish this task. Here is an example to generate Create Table Scripts.

    public string GetTableDescription(string pDatabaseName, string pSchemaName, string pTableName, string connectionString)
    {
        connectionString = ConnectionHelper.RemoveProviderFromConnectionString(connectionString);
        Server server = new Server(new ServerConnection(new SqlConnection(connectionString)));
        Database db = server.Databases[pDatabaseName];
        Table t = db.Tables[pTableName, pSchemaName];
        ScriptingOptions baseOptions = new ScriptingOptions();
        baseOptions.NoCollation = true;
        baseOptions.SchemaQualify = true;
        baseOptions.DriDefaults = true;
        baseOptions.IncludeHeaders = false;
        baseOptions.DriPrimaryKey = true;
        baseOptions.ExtendedProperties = true;
        //baseOptions.DriAll = true;
        //baseOptions.Indexes = true;
        //baseOptions.DriAllKeys = true;
        //baseOptions.SchemaQualifyForeignKeysReferences = true;
        baseOptions.EnforceScriptingOptions = true;
        StringCollection result = t.Script(baseOptions);
        return StringCreate(result);
    }

First, stop using SELECT * in your views. I talk about this quite a bit here:

• Bad habits to kick : using SELECT * / omitting the column list

Next, run sp_refreshview or sp_refreshsqlmodule against each view that references a table (or another view!) that you have changed, e.g.:

EXEC sp_refreshview N'dbo.viewname';

If you want to generate a script that refreshes all of the views in the system:

DECLARE @sql NVARCHAR(MAX) = N'';

SELECT @sql += N'
  EXEC sp_refreshsqlmodule N''' 
  + QUOTENAME(s.name) + '.' + QUOTENAME(v.name) + ''';'
FROM sys.schemas AS s 
INNER JOIN sys.views AS v 
ON s.[schema_id] = v.[schema_id]; 

EXEC sp_executesql @sql;

(You may want to run it twice in case there are circular or out-of-order dependencies.)

But this is a temporary fix. You will have to repeat this every time you change any table that might have views that reference it, then go find out which views are involved. This is not the right way to approach schema design. AT ALL. I wrote about a DDL trigger that would help automate this for you, but this is really the wrong approach - just stop using SELECT * in your views!

• Keeping sysdepends up to date in SQL Server 2008

You may also want to consider adding WITH SCHEMABINDING to the definition of all of your views. This does two things:

1. Prevents you from using SELECT * in the view. This is A GOOD THING™.
2. Prevents you from changing underlying tables without knowing exactly which views would be affected by the change. This is also A GOOD THING™.