jump to navigation

Generate scripts to detach/attach all databases 2011/05/23

Posted by DB Dave in Uncategorized.
Tags: ,
comments closed

I haven’t had time to do any Denali testing since my last post, so instead I thought I’d share a script I wrote a while ago which simply generates a script to attach one or more databases using their current file paths (and optionally then detaches these databases).

The reason I wrote this script in the first place was because we have a number of environment (i.e. dev, test, stage, reporting, etc), and some of these databases are made up of quite a few database files (i.e. more than a dozen or so).
So on the odd occasion when I’ve needed to detach a database, move some files around, and re-attach said database using the Management Studio GUI, I’ve found it frustrating having to manually browse to each file’s location over and over again. And I just prefer doing stuff in T/SQL over using the GUI – makes me feel more in control I think.

So, with this script I could easily generate the “attach” code for each database (including all file paths), make any path or filename changes necessary, and re-attach all the databases with a single mouse click!

USE [master];

DECLARE @database NVARCHAR(200),
@cmd NVARCHAR(1000),
@attach_cmd NVARCHAR(4000),
@file NVARCHAR(1000),
@i INT;

DECLARE dbname_cur CURSOR STATIC LOCAL FORWARD_ONLY FOR
SELECT  RTRIM(LTRIM([name]))
FROM    sysdatabases
WHERE   [dbid] > 4  -- exclude system databases

OPEN dbname_cur
FETCH NEXT FROM dbname_cur INTO @database
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @i = 1;

-- Initial attach command stub
SET @attach_cmd = '-- ' + QUOTENAME(@database) + CHAR(10)
+ 'EXEC sp_attach_db @dbname = ''' + @database + '''' + CHAR(10);

-- Get a list of files for this database
DECLARE dbfiles_cur CURSOR STATIC LOCAL FORWARD_ONLY FOR
SELECT  physical_name
FROM    sys.master_files
WHERE   database_id = db_id(@database)
ORDER   BY [file_id];

OPEN dbfiles_cur
FETCH NEXT FROM dbfiles_cur INTO @file
WHILE @@FETCH_STATUS = 0
BEGIN
SET @attach_cmd = @attach_cmd + '    ,@filename' + CAST(@i AS NVARCHAR(10)) + ' = ''' + @file + '''' + CHAR(10);
SET @i = @i + 1;
FETCH NEXT FROM dbfiles_cur INTO @file
END

CLOSE dbfiles_cur;
DEALLOCATE dbfiles_cur;

-- Output "attach" command.
PRINT @attach_cmd;

-- DETACH (uncomment the following line at your peril - IT WILL DETACH ALL USER DATABASES!)
/*EXEC sp_detach_db @dbname = @database, @skipchecks = 'true';  -- change this to false if you want it to update stats before detaching*/

FETCH NEXT FROM dbname_cur INTO @database
END

CLOSE dbname_cur;
DEALLOCATE dbname_cur;

The usual disclaimers apply; I’ve used this in a limited number of scenarios so I wouldnt consider it thoroughly tested, and if you run it in a production environment and “accidentally” detach databases, or otherwise break something – don’t blame me.

Cheers
Dave

Advertisements

Identifying unique indexes that aren’t defined as unique 2011/05/16

Posted by DB Dave in Uncategorized.
Tags: , ,
comments closed

Confused? So am I, but after changing the title of this post about a dozen times this is what we’re left with… 🙂

I was in the process of examining some indexes on our test Denali server (see previous posts) when I decided to check how many of our non-unique, non-clustered indexes are actually made up of a unique column (or a unique combination of columns in the case of a compound index). In other words, which non-clustered indexes could I convert to UNIQUE non-clustered indexes? I whipped up a script to do this and thought I’d share.

If you’re wondering why I might want to do this; I’m interested to see what impact it has on index storage, as well as read performance. I’m by no means saying you should go and recreate all of your NC indexes as unique – remember that this script will return all indexes where the *current* data in the table is distinct. There are no guarantees that future inserts & updates won’t cause the data to become non-unique (and break your application).

Here’s the script. It’s pretty straight forward, and simply returns a list of tables/indexes at the moment, but could easily be change to actually output the CREATE / DROP_EXISTING commands instead.

SET NOCOUNT ON;
DECLARE @i INT = 1,
        @rc INT,
        @cmd NVARCHAR(2000),
        @tablename NVARCHAR(128),
        @indexname NVARCHAR(128),
        @collist NVARCHAR(1000),
        @rowcount INT,
        @rowcount_d INT;

DECLARE @tt_ncidxs TABLE (rowid INT IDENTITY, [object_id] INT, [object_name] VARCHAR(128), [index_id] INT, index_name VARCHAR(128), [index_columns] VARCHAR(1000), is_unique BIT);

WITH IndexColumns AS 
(
    SELECT  DISTINCT 
            i.[object_id],
            QUOTENAME(OBJECT_NAME(i.[object_id])) AS [object_name],
            i.[index_id],
            QUOTENAME(i.[name]) AS [index_name],
            STUFF(  
                    (
                    SELECT  ', ' + QUOTENAME(COL_NAME([object_id], column_id)) AS 'data()'
                    FROM    sys.index_columns AS t2  
                    WHERE   c.[object_id] = t2.[object_id]  
                    AND     c.index_id = t2.index_id 
                    FOR     XML PATH ('')
                    ), 1, 2, ''
                ) AS 'index_columns'
    FROM    sys.indexes AS i  
    JOIN    sys.index_columns AS c ON c.[index_id] = i.[index_id] AND c.[object_id] = i.[object_id]
    JOIN    sys.objects AS so ON i.[object_id] = so.[object_id] 
    WHERE   i.is_unique = 0
    AND     i.is_unique_constraint = 0
    AND     so.is_ms_shipped = 0
    AND     so.[type] = 'U'
)
INSERT  @tt_ncidxs ([object_id], [object_name], [index_id], [index_name], [index_columns])
SELECT  [object_id],
        [object_name],
        [index_id],
        [index_name],
        [index_columns]
FROM    IndexColumns 

--SELECT * FROM @tt_ncidxs
SELECT @rc = @@ROWCOUNT;

WHILE @i <= @rc
BEGIN
    SELECT @cmd = NULL, @rowcount = NULL, @rowcount_d = NULL, @tablename = NULL, @collist = NULL;
    
    SELECT  @tablename = [object_name],
            @indexname = index_name,
            @collist = [index_columns]
    FROM    @tt_ncidxs
    WHERE   rowid = @i;

    RAISERROR('Table %s column list: %s', 10, 1, @tablename, @collist) WITH NOWAIT;

    SET @cmd = 'SELECT @x_rowcount = COUNT(*) FROM ' + @tablename + ' WITH (NOLOCK);'
    EXEC sp_executesql @cmd, N'@x_rowcount INT OUTPUT', @x_rowcount = @rowcount OUTPUT;

    SET @cmd = 'SELECT @x_rowcount_d = COUNT(*) FROM (SELECT DISTINCT ' + @collist + ' FROM ' + @tablename + ' WITH (NOLOCK) ) x;'
    EXEC sp_executesql @cmd, N'@x_rowcount_d INT OUTPUT', @x_rowcount_d = @rowcount_d OUTPUT;

    RAISERROR('Index %s on table %s selectivity = %i/%i', 10, 1, @indexname, @tablename, @rowcount_d, @rowcount) WITH NOWAIT;

    IF (ISNULL(@rowcount, 1) = ISNULL(@rowcount_d, 2)) -- i.e. column values are (currently) distinct/unique
        UPDATE  @tt_ncidxs
        SET     is_unique = 1
        WHERE   rowid = @i;

    SET @i += 1;
END

-- Get a list of non-unique nonclustered indexes whose index columns are currently distinct.
-- These *may* be candidates for conversion to UNIQUE nonclustered indexes instead.
-- NB: PLEASE MAKE 100% SURE THAT THE COLUMNS ARE ALWAYS GOING TO BE UNIQUE BEFORE CREATING A UNIQUE INDEX
SELECT  *
FROM    @tt_ncidxs
WHERE   is_unique = 1
ORDER   BY [object_name], [index_id]

Cheers
DB Dave