please help !! Dynamic SQL

• laurie-789651

  SSCertifiable

  Points: 7680

  More actions

  August 8, 2018 at 9:03 am

  #2000836

  Lynn Pettis - Wednesday, August 8, 2018 8:23 AM

  Try this, uncomment the EXEC statement.
  
DECLARE @SQLTemplate NVARCHAR(MAX)
        , @SQLCmd NVARCHAR(MAX)
        , @SQLParm NVARCHAR(MAX) = N'@iViewDef NVARCHAR(MAX)'
        , @ViewName NVARCHAR(256)
        , @ViewDef NVARCHAR(MAX)
        , @DBName NVARCHAR(256);

  SET @DBName = QUOTENAME(N'Test');
  SET @SQLTemplate = N'
  USE !DBName!;

  IF OBJECT_ID(''!ViewName!'') IS NOT NULL
    DROP VIEW !ViewName!;

  exec [sys].[executesql] @iViewDef
  ';

  DECLARE [ViewDef] CURSOR LOCAL FORWARD_ONLY FAST_FORWARD READ_ONLY
  FOR
  SELECT
    QUOTENAME(OBJECT_SCHEMA_NAME([v].[object_id])) + '.' + QUOTENAME([v].[name]) AS [Viewname]
    , [sm].[definition]                   AS [ViewDefinition]
  FROM
    [sys].[views]                  AS [v]
    INNER JOIN [sys].[sql_modules] AS [sm]
      ON [sm].[object_id] = [v].[object_id]
  ORDER BY
    [v].[name];

  OPEN [ViewDef];

  WHILE 1 = 1
  BEGIN
    FETCH NEXT FROM [ViewDef]
    INTO @ViewName, @ViewDef;

    IF @@FETCH_STATUS <> 0 BREAK; -- Exit loop on error, should enhance and use try catch

    SELECT @SQLCmd = REPLACE(REPLACE(REPLACE(@SQLTemplate,'!ViewName!',@ViewName),'!ViewDef!',@ViewDef),'!DBName!',@DBName)
    PRINT @SQLCmd;
    --EXEC [sys].[sp_executesql] @SQLCmd, @SQLParm, @iViewDef = @ViewDef;
  END

  CLOSE [ViewDef];

  DEALLOCATE [ViewDef];
  GO
  

  Sorry to nitpick, but -

  You need "exec [sys].[sp_executesql] @iViewDef" instead of "exec [sys].[executesql] @iViewDef".

  Other than that it's great! Hope the OP likes it...

• Lynn Pettis

  SSC Guru

  Points: 442447

  More actions

  August 8, 2018 at 9:11 am

  #2000842

  laurie-789651 - Wednesday, August 8, 2018 9:03 AM

  Lynn Pettis - Wednesday, August 8, 2018 8:23 AM

  Try this, uncomment the EXEC statement.
  
DECLARE @SQLTemplate NVARCHAR(MAX)
        , @SQLCmd NVARCHAR(MAX)
        , @SQLParm NVARCHAR(MAX) = N'@iViewDef NVARCHAR(MAX)'
        , @ViewName NVARCHAR(256)
        , @ViewDef NVARCHAR(MAX)
        , @DBName NVARCHAR(256);

  SET @DBName = QUOTENAME(N'Test');
  SET @SQLTemplate = N'
  USE !DBName!;

  IF OBJECT_ID(''!ViewName!'') IS NOT NULL
    DROP VIEW !ViewName!;

  exec [sys].[executesql] @iViewDef
  ';

  DECLARE [ViewDef] CURSOR LOCAL FORWARD_ONLY FAST_FORWARD READ_ONLY
  FOR
  SELECT
    QUOTENAME(OBJECT_SCHEMA_NAME([v].[object_id])) + '.' + QUOTENAME([v].[name]) AS [Viewname]
    , [sm].[definition]                   AS [ViewDefinition]
  FROM
    [sys].[views]                  AS [v]
    INNER JOIN [sys].[sql_modules] AS [sm]
      ON [sm].[object_id] = [v].[object_id]
  ORDER BY
    [v].[name];

  OPEN [ViewDef];

  WHILE 1 = 1
  BEGIN
    FETCH NEXT FROM [ViewDef]
    INTO @ViewName, @ViewDef;

    IF @@FETCH_STATUS <> 0 BREAK; -- Exit loop on error, should enhance and use try catch

    SELECT @SQLCmd = REPLACE(REPLACE(REPLACE(@SQLTemplate,'!ViewName!',@ViewName),'!ViewDef!',@ViewDef),'!DBName!',@DBName)
    PRINT @SQLCmd;
    --EXEC [sys].[sp_executesql] @SQLCmd, @SQLParm, @iViewDef = @ViewDef;
  END

  CLOSE [ViewDef];

  DEALLOCATE [ViewDef];
  GO
  

  Sorry to nitpick, but -

  You need "exec [sys].[sp_executesql] @iViewDef" instead of "exec [sys].[executesql] @iViewDef".

  Other than that it's great! Hope the OP likes it...

  Already fixed that.

• Joe Torre

  SSChampion

  Points: 10248

  More actions

  August 9, 2018 at 6:09 pm

  #2001148

  Lidou123 - Tuesday, August 7, 2018 9:47 AM

  laurie-789651 - Tuesday, August 7, 2018 8:45 AM

  Are you still trying to do this?
  What is the purpose?  Do you want to move views from one database to another?

  Jeff Moden - Tuesday, August 7, 2018 9:07 AM

  Lidou123 - Tuesday, August 7, 2018 8:55 AM

  laurie-789651 - Tuesday, August 7, 2018 8:45 AM

  Are you still trying to do this?
  What is the purpose?  Do you want to move views from one database to another?

  Hello.
  Yes. Still doing it.
  Do u have something to help me ?

  If you're trying to move the whole database, why not just do a restore of the existing database?

  Hello
  Because I don't want to do the tasks manually.  I just want to duplicate the views and the procedures.

  Why not script it out in SSMS?

• Lynn Pettis

  SSC Guru

  Points: 442447

  More actions

  August 10, 2018 at 10:59 am

  #2001279

  Joe Torre - Thursday, August 9, 2018 6:09 PM

  Lidou123 - Tuesday, August 7, 2018 9:47 AM

  laurie-789651 - Tuesday, August 7, 2018 8:45 AM

  Are you still trying to do this?
  What is the purpose?  Do you want to move views from one database to another?

  Jeff Moden - Tuesday, August 7, 2018 9:07 AM

  Lidou123 - Tuesday, August 7, 2018 8:55 AM

  laurie-789651 - Tuesday, August 7, 2018 8:45 AM

  Are you still trying to do this?
  What is the purpose?  Do you want to move views from one database to another?

  Hello.
  Yes. Still doing it.
  Do u have something to help me ?

  If you're trying to move the whole database, why not just do a restore of the existing database?

  Hello
  Because I don't want to do the tasks manually.  I just want to duplicate the views and the procedures.

  Why not script it out in SSMS?

  Because that would be a manual process?  Just guessing.