Posts Tagged 'SSMS 2008'

Snippets for SQL Server Management Studio (SSMS)

How many times have you had to open an existing SQL object and copy the header information to add to a new procedure, view or function? How many times have you done this and forgot to change the information in the header? Do you like standards? If you answered “Yes!” to any of these questions then templates are for you! Microsoft SQL Server Management Studio comes with a plethora of built-in templates, but this little gem of a feature also allows you to create your own!

To open template explorer, select View -> Template Explore or press Ctrl+Alt+T. This will open a side bar (by default, but you can change it) with the templates installed by default. To add your own templates, right-click on “SQL Server Templates” and then “New” from the context menu. This will give you the option to create a new folder or template. I prefer to keep my user-created templates in their own folder, since that makes it easier to transfer templates between machines.

Let’s create a folder called “CodeSnippets” as shown in the screen shot below.

Template Context Menu (Folder)

Template Context Menu (Folder)

Now that we have our folder created, let’s create some templates. We will create a basic header template and a stored procedure template. To create our templates, Right click on the newly create folder and select New -> Template from the context menu.

Template Context Menu (Template)

Template Context Menu (Template)

Now that we have the blank templates created, let’s add some content. Right click on the template and select “Edit” from the context menu.

Template Context Meny (Edit)

Template Context Meny (Edit)

From here you can write or paste your content into the template.After creating your templates, usage is as simple as dragging and dropping your template into the proper location in the query window.

I mentioned earlier about transferring templates between machines. To move your templates between machines browse to

C:\Documents and Settings\[User Profile]\Application Data\Microsoft\Microsoft SQL Server\[90 for SQL 2005 or 100 for SQL 2008]\Tools\Shell\Templates\Sql\[Your Snippet Folder Name]

for Windows XP and Windows Server 2003 or

C:\Users\[User Profile]\AppData\Roaming\Microsoft\Microsoft SQL Server\[90 for SQL 2005 or 100 for SQL 2008]\Tools\Shell\Templates\Sql\[Your Snippet Folder Name]

for Windows Vista, Windows 7 and Windows Server 2008. (Please replace the items in [] with your specific value for your environment) and copy the data to the appropriate path on the source machine.

I hope you find this as useful as I do! Happy coding!

~Ron

Advertisements

Updated: Attach Database Scripts

I updated my previous version of this script, which was written for SQL 2005, to handle filestream data. And I added a header for Ignas.

Please post in the comments if you run in to any issues.

~Ron

/******************************************************************************
    Name: Create Each Database for Attach
    Author: Ron Carpenter
    Date: 08/18/2009
    Revision: 1.0
    Purpose: This script will generate scripts to create each database on a
             sever for attach. It handles multiple files, multiple files,
             multiple files on a filegroup and multiple log files. There is a
             parameter to attach the logs or create the databases for attach
             with rebuild log.

    Author: Ron Carpenter
    Date: 01/20/2010
    Revision: 1.1
    Purpose: Updated the script to handle filestream data.
******************************************************************************/
USE [master] ;
GO
CREATE TABLE #_dba_migrate_db
    (
     FileID int IDENTITY(1,1)
                NOT NULL,
     DBName varchar(255) NOT NULL,
     DBGroupName varchar(255) NOT NULL,
     DBType tinyint NOT NULL,
     DBFileLogicalName varchar(255) NOT NULL,
     DBFileName varchar(1000) NOT NULL,
     DBSize varchar(15) NOT NULL,
     DBMaxSize varchar(15) NOT NULL,
     DBFileGrowth varchar(15) NOT NULL,
     CONSTRAINT PK_#_dba_migrate_db PRIMARY KEY CLUSTERED (DBType ASC,FileID ASC)
    ) ;
GO
sp_msforeachdb
'
USE [?] ;

IF DB_NAME() NOT IN (''master'',''model'',''msdb'',''tempdb'')
    INSERT INTO
        #_dba_migrate_db
        (
         DBName,
         DBGroupName,
         DBType,
         DBFileLogicalName,
         DBFileName,
         DBSize,
         DBMaxSize,
         DBFileGrowth
        )
        SELECT
            DB_NAME(),
            COALESCE(fg.name,''LOG''),
            f.type,
            LTRIM(RTRIM(f.name)),
            LTRIM(RTRIM(f.physical_name)),
            CAST(CEILING(((CONVERT(decimal(25,2),f.size) / 1024) * 8)) AS varchar(15)) + ''MB'',
            CASE f.max_size
              WHEN-1 THEN ''UNLIMITED''
              ELSE CAST(CEILING(((CONVERT(decimal(25,2),f.max_size) / 1024) * 8)) AS varchar(15)) + ''MB''
            END,
            CASE f.is_percent_growth
              WHEN 1 THEN CAST(f.growth AS varchar(15)) + ''%''
              ELSE CAST(CEILING(((CONVERT(decimal(25,2),f.growth) / 1024) * 8)) AS varchar(15)) + ''MB''
            END
        FROM
            sys.database_files f
        LEFT OUTER JOIN sys.filegroups fg
            ON f.data_space_id = fg.data_space_id ;
'
GO

SET NOCOUNT ON ;

SELECT
    'USE [master] ;' + CHAR(10) + 'GO' + CHAR(10) ;

DECLARE
    @DBName varchar(255),
    @DBGroupName varchar(255),
    @DBType int,
    @FileID int,
    @MaxData int,
    @MaxLog int,
    @MaxFileStrem int,
    @IncludeLog bit ;

SET @IncludeLog = 1 ;

DECLARE db_cursor CURSOR
    FOR SELECT DISTINCT
            DBName
        FROM
            #_dba_migrate_db

OPEN db_cursor ;

FETCH NEXT FROM db_cursor INTO @DBName ;
WHILE @@FETCH_STATUS = 0
    BEGIN
        SELECT
            'CREATE DATABASE ' + @DBName + ' ON'

        DECLARE group_cursor CURSOR
            FOR SELECT
                    MIN(FileID),
                    CASE DBType
                      WHEN 2 THEN 0
                      ELSE DBType
                    END,
                    DBGroupName
                FROM
                    #_dba_migrate_db
                WHERE
                    DBName = @DBName AND
                    (DBType <> 1 OR @IncludeLog = 1)
                GROUP BY
                    DBGroupName,
                    DBType
                ORDER BY
                    CASE DBType
                      WHEN 2 THEN 0
                      ELSE DBType
                    END,
                    MIN(FileID) ;

        OPEN group_cursor ;

        FETCH NEXT FROM group_cursor INTO @FileID,@DBType,@DBGroupName ;
        WHILE @@FETCH_STATUS = 0
            BEGIN
                SELECT
                    @MaxData = MAX(FileID)
                FROM
                    #_dba_migrate_db
                WHERE
                    DBName = @DBName AND
                    DBType IN (0,2) ;

                SELECT
                    @MaxLog = MAX(FileID)
                FROM
                    #_dba_migrate_db
                WHERE
                    DBName = @DBName AND
                    DBType = 1 ;

                SELECT
                    CASE WHEN DBType = 0 AND DBGroupName = 'PRIMARY' THEN DBGroupName
                         WHEN DBType = 1 THEN DBGroupName + ' ON'
                         WHEN DBType = 2 THEN 'FILEGROUP ' + DBGroupName + ' CONTAINS FILESTREAM'
                         ELSE 'FILEGROUP ' + DBGroupName
                    END
                FROM
                    #_dba_migrate_db
                WHERE
                    FileID = @FileID ;

                SELECT
                    '(' + CHAR(10) + '	NAME = ' + DBFileLogicalName + ', ' + CHAR(10) + '	FILENAME = ''' + DBFileName + CASE WHEN DBType <> 2 THEN ''',' + CHAR(10) + '	SIZE = ' + DBSize + ',' + CHAR(10) + '	MAXSIZE = ' + DBMaxSize + ',' + CHAR(10) + '	FILEGROWTH = ' + DBFileGrowth
                                                                                                                             ELSE ''''
                                                                                                                        END + CHAR(10) + ')' + CASE WHEN FileID IN (@MaxData,@MaxLog) THEN ''
                                                                                                                                                    ELSE ','
                                                                                                                                               END
                FROM
                    #_dba_migrate_db
                WHERE
                    DBName = @DBName AND
                    DBGroupName = @DBGroupName ;

                FETCH NEXT FROM group_cursor INTO @FileID,@DBType,@DBGroupName ;
            END

        CLOSE group_cursor ;

        DEALLOCATE group_cursor ;

        SELECT
            CASE WHEN @IncludeLog = 1 THEN 'FOR ATTACH;'
                 ELSE 'FOR ATTACH_REBUILD_LOG;'
            END + CHAR(10) + 'GO' + CHAR(10) ;

        FETCH NEXT FROM db_cursor INTO @DBName ;
    END

CLOSE db_cursor ;

DEALLOCATE db_cursor ;

DROP TABLE #_dba_migrate_db ;

Disable Auto-Recover in SSMS

I recently upgraded to SQL Server Management Studio 2008. (I LOVE the Script as … Drop and Create functionality.) Everything was working great at first, but after a while I noticed that management studio was freezing up on for a couple of seconds every minute. This got progressively worse throughout the day.

Then I noticed that this freeze coincided with the Auto Recover save time. I had never messed with this setting before, so I looked around to see where to change it.

Apparently there is no way to change this in the GUI, but you can change it in the registry.

SQL 2005

Windows Registry Editor Version 5.00

[HKEY_CURRENT_USER\Software\Microsoft\Microsoft SQL Server\90\Tools\Shell\General\AutoRecover]
"AutoRecover Always Enabled"=dword:00000000
"AutoRecover Enabled"=dword:00000000

SQL 2008

Windows Registry Editor Version 5.00

[HKEY_CURRENT_USER\Software\Microsoft\Microsoft SQL Server\100\Tools\Shell\General\AutoRecover]
"AutoRecover Always Enabled"=dword:00000000
"AutoRecover Enabled"=dword:00000000

~Ron