udfGetColumnList( )

USE Admin

IF OBJECT_ID('dbo.udfGetColumnList') Is Not Null

      DROP FUNCTION dbo.udfGetColumnList

go

CREATE FUNCTION dbo.udfGetColumnList(

      @ObjName sysname,

      @UseBrackets bit

      )

RETURNS varchar(max)

AS

/*    DATE        AUTHOR            REMARKS

      9/9/14            PPaiva                  Initial creation.

      DESCRIPTION

            Returns a comma-separated list of columns based

                  on @ObjName from vwDictCol.

      DEPENDENCY

            vwDictCol

      USAGE

            SELECT dbo.udfGetColumnList('Employee', 1)

            SELECT * FROM vwDictCol where ObjName = 'Employee'

            SELECT dbo.udfGetColumnList(TableName, 1) ColList,

                        *

            FROM vwRowCount

      DEBUG

            SELECT *

            FROM vwDictCol

           

*/

BEGIN

      DECLARE @t TABLE( ColID int,

                                    ColName sysname

                                    )

      DECLARE @MaxColID int,

                  @ColID int = 0,

                  @ColName sysname,

                  @Out varchar(max) = ''

     

     

      INSERT INTO @t

            SELECT ColID, ColName

            FROM vwDictCol

            WHERE ObjName = @ObjName

            ORDER BY 1

      SELECT  @MaxColID = Max(ColID)                 

      FROM @t

      WHILE @ColID < @MaxColID

            BEGIN

                  -- Get next ColID

                  SELECT @ColID = Min(ColID)

                  FROM @t

                  WHERE ColID > @ColID

                  -- Get ColName

                  SELECT @ColName = ColName

                  FROM @t

                  WHERE ColID = @ColID

                  IF @UseBrackets = 1

                        SET @ColName = '[' + @ColName + ']'

                  SET @Out += @ColName

                  IF @ColID <> @MaxColID

                        SET @Out += ', '

                       

            END

      RETURN @Out

END

udfGetFilenameAndExtOnly

Useful function for returning only the filename with its extension.  That is, the path is not shown.

This is a dependent object for some other objects on this blog.

USE Admin

IF Object_ID('dbo.udfGetFilenameAndExtOnly') Is Not Null

      DROP FUNCTION dbo.udfGetFilenameAndExtOnly

go

CREATE FUNCTION dbo.udfGetFilenameAndExtOnly(

      @In varchar(1000)

      )

RETURNS varchar(300)

AS

/*    DATE              AUTHOR            REMARKS

      8/22/14          PPaiva            Initial creation.

      DESCRIPTION

            Returns filename and extention only.  That is, path is stripped away.

      USAGE

            SELECT dbo.udfGetFilenameAndExtOnly('O:\Program Files\Microsoft SQL Server\MSSQL10.HOLD\MSSQL\DATA\MSDBData.mdf')

            SELECT dbo.udfGetFilenameAndExtOnly('Admin_Log.ldf')

      DEBUG

            -- Last backslash, in reverse

            SELECT CharIndex('\', Reverse('O:\Program Files\DATA\MSDBData.mdf'))

            -- Pos of last backslash

            SELECT Len('O:\Program Files\DATA\MSDBData.mdf') - 13

*/

BEGIN

      DECLARE @Out varchar(300),

                  @Pos smallint

           

      -- Strip away path if it exists

      -- Get position of last backslash

      SET @Pos = CharIndex('\', Reverse(@In))

      IF @Pos > 1

            BEGIN

                  SET @Pos = Len(@In) - @Pos + 2

                  SET @Out = Substring(@In, @Pos, 500)

            END

      ELSE

            SET @Out = @In

      RETURN @Out

END

RestoreScriptGenerateFromBak - Generate restore script from backup file

Here’s a handy-dandy proc for restoring a database backup file.  You simply pass in the path of the backup, your new DB name, and the locations for data and log files. 

This is especially helpful when your database has multiple files (10, 20, 50 files) due to partitioning or separated file groups.  But even if you only have 2 files, it is still a time-saver.

USE Admin

IF OBJECT_ID('dbo.RestoreScriptGenerateFromBak') Is Not Null

      DROP PROC dbo.RestoreScriptGenerateFromBak

GO

CREATE PROC dbo.RestoreScriptGenerateFromBak

      @PathFilename varchar(300),

      @NewDBName varchar(100),

      @DataPath varchar(200),

      @LogPath varchar(200)

AS

/*    DATE        AUTHOR            REMARKS

      4/16/14           PPaiva            Initial creation.

     

      DESCRIPTION

            Creates a restore script for a given backup file

                  with a common specified destination folder for data

                  files, and another for log files.

            This is especially useful when making a copy of a multi-file

                  (10, 50, 100+ files) database, as in the case of

                  partitioning and/or multiple file groups.

            Click on Messages tab to review script.

     

      DEPENDENCIES

            Admin.dbo.udfGetFilenameOnly()

            RESTORE FILELISTONLY

     

      USAGE

            Exec RestoreScriptGenerateFromBak

                  'C:\Sql\Bak\ProdDB_20121116.bak',

                  'MyDevDB',

                  'E:\Sql\Data',

                  'L:\Sql\Logs'

            Exec RestoreScriptGenerateFromBak

                  'C:\Paul\SqlDataFromClient\Sopa\Sopa_Feb27.bak',

                  'SopaFeb27',

                  'E:\Data',

                  'F:\Logs'

      DEBUG

            RESTORE FILELISTONLY

            FROM DISK = 'C:\Paul\SqlDataFromClient\Sopa\Sopa_Feb27.bak'

           

*/

SET NOCOUNT ON

DECLARE @Sql varchar(Max),

            @crlf varchar(2),

            @Path varchar(200)

           

SET @crlf = CHAR(13) + CHAR(10)

SET @DataPath = dbo.udfAddBackslashIfNec(@DataPath)

SET @LogPath = dbo.udfAddBackslashIfNec(@LogPath)

CREATE TABLE #FileList(

      LogicalName nvarchar(128) NOT NULL PRIMARY KEY,

      PhysicalName nvarchar(260),

      Type char(1),

      FileGroupName nvarchar(128),

      Size numeric(20,0),

      MaxSize numeric(20,0),

      FileID bigint,

      CreateLSN numeric(22,0),

      DropLSN numeric(25,0),

      UniqueID uniqueidentifier,

      ReadOnlyLSN numeric(25,0),

      ReadWriteLSN numeric(25,0),

      BackupSizeInBytes bigint,

      SourceBlockSize int,

      FileGroupID int,

      LogGroupGUID uniqueidentifier,

      DifferentialBaseLSN numeric(25,0),

      DifferentialBaseGUID uniqueidentifier,

      IsReadOnly bit,

      IsPresent bit,

      TDEThumbprint varbinary(32)

      )

-- Determine version of SQL

DECLARE @Version varchar(10) 

SELECT @Version = Convert(varchar(10), ServerProperty('ProductVersion'))

IF Left(@Version, 2) = '9.'

      ALTER TABLE #FileList DROP COLUMN TDEThumbprint

     

SET @Sql = 'RESTORE FILELISTONLY FROM DISK = ''' + @PathFilename + ''''

     

INSERT INTO #FileList

      Exec (@Sql)

SELECT *

FROM #FileList

ORDER BY FileID

DECLARE @LogicalName nvarchar(128),

            @Filename varchar(300),

            @FileID int,

            @MaxFileID int,

            @Type char(1),

            @NumIterations int

SELECT  @MaxFileID = MAX(FileID),

            @FileID = 0,

            @NumIterations = 0,

            @Sql = 'RESTORE DATABASE [' + @NewDBName + ']

FROM DISK = ''' + @PathFilename + '''

WITH '

FROM #FileList

WHILE @FileID < @MaxFileID

      BEGIN

            SET @NumIterations = @NumIterations + 1

     

            -- Get next file

            SELECT @FileID = MIN(FileID)

            FROM #FileList

            WHERE FileID > @FileID

     

            -- Get other attribs for this FileID

            SELECT  @LogicalName = LogicalName,

                        @Filename = dbo.udfGetFilenameOnly(PhysicalName),

                        @Type = Type

            FROM #FileList

            WHERE FileID = @FileID

     

            IF @NumIterations = 1

                  SET @Sql = @Sql

            ELSE

                  SET @Sql = @Sql + '     '

     

            --SELECT @Type Type, @DataPath DataPath, @LogPath LogPath

                 

            IF @Type = 'D'

                  SET @Path = @DataPath

            ELSE

                  BEGIN

                        SET @Path = @LogPath

           

                        -- Force suffix to be .ldf if a log file

                        SET @Filename = REPLACE(@Filename, '.ndf', '.ldf')

                        SET @Filename = REPLACE(@Filename, '.mdf', '.ldf')

                  END

           

            SET @Sql = @Sql + 'MOVE ''' + @LogicalName + ''' TO ''' +  @Path + @NewDBName + '_' + @Filename + ''',' + @crlf

           

     

      END

-- Remove last comma

SET @Sql = LEFT(@Sql, len(@Sql) - 3)

Print @Sql