Tuesday, July 22, 2014

Quick Query to Display Database by Dataspace, Table, and Index

This query just breaks down the current database by dataspace, table, and index, giving you some useful information such as size in MB, pathname of the dataspace file, etc.

-- Shows size of each object in each dataspace in the current database. 
  select db_name()                              as DatabaseName
       , ds.name                                as DataSpaceName
       , ds.type_desc                           as DataSpaceTypeDesc
       , o.name                                 as TableName
       , i.name                                 as IndexName
       , i.type_desc                            as IndexDesc
       , (sum(ps.used_page_count) * 8) / 1024   as IndexSizeMB
       , df.physical_name                       as PathName
    from sys.objects                 o
    join sys.indexes                 i 
      on o.object_id = i.object_id 
    join sys.data_spaces             ds 
      on ds.data_space_id = i.data_space_id
    join sys.database_files          df
      on ds.data_space_id = df.data_space_id
    join sys.dm_db_partition_stats   ps
      on i.object_id = ps.object_id
group by ds.name
       , o.name
       , i.name
       , ds.type_desc
       , i.type_desc
       , df.physical_name
order by ds.name
       , o.name
       , i.name

Wednesday, July 2, 2014

Use Central Management Servers to Alter a Column in All Servers

The "Central Management Servers" functionality of SQL Server Management Studio is an excellent way to apply the same code to every server. By "every server", I mean groups of servers that you define: you might have different servers in folders named "Production", "Development", "QA", "SQL Server 2000", etc. It just depends on your needs.

To get to the "Central Management Servers" pane in SSMS, simply select the "View, Registered Servers" menu item. After you've set up your servers, you can execute queries against every database in every server. The example below changes the width of a column in a table. Note that in this example, we look for only databases whose name begins with "DB100", we exclude read-only databases, and we specify the schema, table, and column name we want to modify. The code also checks that the length isn't already set to the value we want, to avoid unnecessary noise in the output. This code is compatible with SQL Server 2000 and later (hence the use of "syscolumns", etc.)

-- Comment. 
set nocount on
declare @Sql nvarchar(4000)

set @Sql = 'use [?]

            if exists (select *
                         from syscolumns   sc
                         join sysobjects   so
                           on sc.id = so.id
                        where ''?''                                   like ''DB100%''
                          and databaseproperty(''?'', ''IsReadOnly'')    = 0
                          and user_name(so.uid)                          = ''dbo''
                          and object_name(sc.id)                         = ''Customer''
                          and sc.name                                    = ''FirstName''
                          and sc.length                                 != 120)
            begin
                select ''?'', sc.length
                  from syscolumns   sc
                  join sysobjects   so
                    on sc.id = so.id
                 where ''?''                                   like ''DB100%''
                   and databaseproperty(''?'', ''IsReadOnly'')    = 0
                   and user_name(so.uid)                          = ''dbo''
                   and object_name(sc.id)                         = ''Customer''
                   and sc.name                                    = ''FirstName''
                   and sc.length                                 != 120

                ALTER TABLE dbo.Customer ALTER COLUMN FirstName VARCHAR(120) NULL
            end '

exec sp_msforeachdb @Sql
go

References

For more information on Central Management Servers:

     Create a Central Management Server and Server Group

     Registered Servers and Central Management Server Stores

     Execute SQL Server query on multiple servers at the same time

Friday, June 7, 2013

SSMS 2012 - Lines with Background Color Have Ugly Gaps

I've been using SQL Server Management Studio 2012 for about 10 minutes now, so I don't have much to say about it yet. I'm sure it's a big improvement over previous versions, and I'm looking forward to exploring.

So what's the problem? It's trivial, really, but at the same time, just so glaringly ugly. I set my T-SQL comments to have a background color. For me, it's easier to "filter out" the non-executing part of the code that way. It also makes it possible to "section off" my code for clarity; splitting a long script into chunks makes it much more digestable. Here's an example using SSMS 2008:


See how the comments form one cohesive block of color? Smarter people than me could tell you why this is psycho-visually important. I just know that it is important. So imagine my dismay when I opened my first SQL file in SSMS 2012 and saw this (click on the image to get the full-size version):


Each comment line is now separated by a thin stripe of background color, in my case black. (And yes, it looks the same with a white background.) Who at Microsoft thought this monstrosity was acceptable? The only Connect I could find on it is at http://connect.microsoft.com/VisualStudio/feedback/details/645507/syntax-highligher-issues and it's been marked as "Closed Won't Fix," which is the kind of response that reminds me why I'm not a full-time a C/C++/C# developer anymore.

I know, it's a little thing, but a closer look at these lines reveals that someone put some work into creating this gap: it wasn't just accidental. Look closely at the line above the black gap in the zoomed-in image below:


You can see that the color of the line above the gap is blended with the background color, which, I suppose, gives it a more appealing look (assuming that you like the background lines in the first place). This touch is obviously someone's handiwork, which probably explains why they're not interested in fixing it. I guess now I just need to either live it with, stop coloring comments differently, or find a workaround somewhere.

Thursday, May 30, 2013

Find All NOT NULL Columns Without a Default Constraint

Rarely, I'll run into a situation where it would be useful to know which columns that are specified as NOT NULL don't have a default constraint as well. For example, I've seen a table with two dozen columns, all but a few set as NOT NULL. This in itself isn't an issue, but on this occasion, for debugging purposes, we were constantly INSERTing a test row of data into the table. Having to specify a default value for each column got to be annoying after a while.

There are, of course, other ways to skin this cat, We could have created a SSMS template and used the old "Ctrl-Shift-M" key combo to type in just the values we needed. Or we could have created a script, or even a stored procedure to handle the default values for us. For various reasons, those solutions wouldn't work in our environment, so I decided to attack the problem at its root.

The code below looks at all non-empty tables and prints out T-SQL code to add the default constraint to each column it finds. Note that I've made an attempt to select a reasonable default value for all the common data types, but (a) the list is not complete, and (b) your idea of a "reasonable" default value may be very different from mine. So don't just copy and paste the output from this script and hit F5 - take a moment to read each line and be sure it does what you want; if not, you can easily change it!

-------------------------------------------------------------------------------
-- For all tables in the current database that have rows, finds all "NOT NULL" 
-- columns that don't have a DEFAULT constraint, and emits the ALTER TABLE     
-- statements needed to add the DEFAULT constraints for them.                  
-------------------------------------------------------------------------------

SET NOCOUNT ON

DECLARE @SchemaName      sysname         SET @SchemaName     = ''
DECLARE @TableName       sysname         SET @TableName      = ''
DECLARE @ColumnName      sysname         SET @ColumnName     = ''
DECLARE @ColumnType      sysname         SET @ColumnType     = ''
DECLARE @ConstraintName  sysname         SET @ConstraintName = ''
DECLARE @Sql             NVARCHAR(MAX)   SET @Sql            = ''

DECLARE cur CURSOR FOR
    SELECT SCHEMA_NAME(t.schema_id)    AS 'Schema Name'
         , OBJECT_NAME(c.object_id)    AS 'Table Name'
         , c.name                      AS 'Column Name'
         , ty.name                     AS 'Column Type'
      FROM sys.columns   c
      JOIN sys.tables    t
        ON c.object_id = t.object_id
      JOIN sys.types     ty
        ON c.user_type_id = ty.user_type_id
     WHERE c.is_nullable                  = 0
       AND c.is_identity                  = 0
       AND c.is_computed                  = 0
       AND t.type_desc                    = 'USER_TABLE'
       AND t.is_ms_shipped                = 0
       AND ISNULL(c.default_object_id, 0) = 0
       AND 0 < (SELECT SUM(row_count)
                  FROM sys.dm_db_partition_stats
                 WHERE object_id = OBJECT_ID(t.name)   
                   AND (index_id = 0 OR index_id = 1))
  ORDER BY 'Schema Name'
         , 'Table Name'
         , 'Column Name'

OPEN cur

FETCH NEXT FROM cur
 INTO @SchemaName, @TableName, @ColumnName, @ColumnType
        
IF @@FETCH_STATUS = 0
BEGIN        
    PRINT 'USE ' + DB_NAME(0)
    PRINT ' ' 
END

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @ConstraintName = QUOTENAME('DF_'+ @TableName + '_' + @ColumnName)
    
    SET @Sql = ''
             + 'PRINT ''Processing: ' + QUOTENAME(@SchemaName) + '.' + @ConstraintName + ''''
             + ' ALTER TABLE ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@TableName) 
             + ' ADD CONSTRAINT ' + @ConstraintName 
             + ' DEFAULT ('
             +
                CASE @ColumnType
                    WHEN 'char'             THEN ''''''
                    WHEN 'nchar'            THEN ''''''
                    WHEN 'varchar'          THEN ''''''
                    WHEN 'nvarchar'         THEN ''''''
                    WHEN 'sysname'          THEN ''''''
                    WHEN 'xml'              THEN ''''''
                    WHEN 'udtUserID'        THEN ''''''
                    WHEN 'text'             THEN ''
                    WHEN 'ntext'            THEN ''

                    WHEN 'bigint'           THEN '0'
                    WHEN 'int'              THEN '0'
                    WHEN 'smallint'         THEN '0'
                    WHEN 'tinyint'          THEN '0'
                    WHEN 'bit'              THEN '0'

                    WHEN 'real'             THEN '0.0'
                    WHEN 'money'            THEN '0.0'
                    WHEN 'smallmoney'       THEN '0.0'
                    WHEN 'float'            THEN '0.0'
                    WHEN 'decimal'          THEN '0.0'
                    WHEN 'numeric'          THEN '0.0'

                    WHEN 'image'            THEN '0x0'
                    WHEN 'binary'           THEN '0x0'
                    WHEN 'varbinary'        THEN '0x0'
                    
                    WHEN 'uniqueidentifier' THEN '0x0'
                    WHEN 'sql_variant'      THEN '0'
                    WHEN 'hierarchyid'      THEN '''/'''
                    WHEN 'geometry'         THEN '0'
                    WHEN 'geography'        THEN '0'

                    WHEN 'datetime'         THEN 'GETDATE()'
                    WHEN 'date'             THEN 'GETDATE()'
                    WHEN 'time'             THEN 'GETDATE()'
                    WHEN 'datetime2'        THEN 'GETDATE()'
                    WHEN 'datetimeoffset'   THEN 'GETDATE()'
                    WHEN 'smalldatetime'    THEN 'GETDATE()'
                    WHEN 'timestamp'        THEN 'GETDATE()'
                    ELSE                         '-1'
                END
                    
             + ')' 
             + ' FOR ' + QUOTENAME(@ColumnName)

    PRINT @Sql    
    
    FETCH NEXT FROM cur
     INTO @SchemaName, @TableName, @ColumnName, @ColumnType
END

CLOSE cur
DEALLOCATE cur