T-sql – Create a T-sql script JOB SQL-2012 and scheduled

Solution 1:

Script all of your foreign keys out and create the tables on the new server without foreign keys. Load the data, then rerun the scripts to create the foreign keys.

Solution 2:

Backup the database, the restore it to the new server.

Solution 3:

Run a query similar to this and figure out the dependencies for yourself.

Pick the one that sounds like the least work :)

This will require really having fun with escaping and re-escaping single quotes, but I think the technique you're after is:

DECLARE 
  @DB  NVARCHAR(255) = QUOTENAME(N'dbname'),
  @SQL NVARCHAR(MAX);

SET @SQL =  N'EXEC ' + @DB + '.sys.sp_executesql '
+ 'N''CREATE VIEW dbo.vWhatever
AS
  SELECT x = 1, y = ''''x'''', z = GETDATE();''';

EXEC sys.sp_executesql @SQL;

So you're kind of nesting the dynamic SQL; the inner one ensures that the SQL is executed at the target database, not locally.

Another way that I've picked up since this original question appeared:

DECLARE 
  @DB  NVARCHAR(255) = QUOTENAME('dbname'),
  @SQL NVARCHAR(MAX),
  @DBExec NVARCHAR(MAX);

SET @DBExec = @DB + N'.sys.sp_executesql';

SET @SQL = N'CREATE VIEW dbo.whatever
  AS
    SELECT x = 1, y = ''x'', z = GETDATE();';

EXEC @DBExec @SQL;

This is slightly tidier because you don't have to double-nest single quotes.

And here is a slightly tidier way to do it without cursors (well, without all the scaffolding of setting up a cursor). Note that the inner dynamic SQL will only be executed in databases where (a) a categories table exists (b) this view does not already exist and (c) it is not a system database (well, not one of the primary system databases, anyway).

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

SELECT @SQL += NCHAR(13) + NCHAR(10)    
  + N'IF NOT EXISTS (SELECT 1 FROM ' + QUOTENAME(name) 
  + '.sys.views WHERE name = ''vNonhiddenCategories'')
  AND EXISTS (SELECT 1 FROM ' + QUOTENAME(name)
  + '.sys.tables WHERE name = ''categories'')
  BEGIN
    EXEC ' + QUOTENAME(name) + '.sys.sp_executesql N''
    CREATE VIEW dbo.vNonhiddenCategories3
    AS
      SELECT x = 1, y = ''''x'''';''
  END'
  FROM sys.databases 
  WHERE database_id BETWEEN 5 AND 32766;

PRINT @sql;
-- EXEC sp_executesql @sql;

Of course "tidier" is in the eye of the beholder.