One of the attendees of my session on metadata last Saturday asked about how to get at this information.  We were already time constrained at 50 minutes per session so I was not able to fit that query in with the remaining items I wanted to show.

The following query starts with the catalog view sys.partitions.  This might throw people as they would assume that partitioning must be implemented to make use of this view.  One of the architectural changes made in SQL Server 2005 is that all tables are technically on a partition (the value being 1 if non-partitioned).  We also make use of sys.destination_data_spaces, sys.allocation_units, and a few other general catalog views.

SELECT sc.name AS SchemaName
    ,t.name AS ObjectName
    ,i.name AS IndexName
    ,i.type_desc AS IndexType
    ,f.name AS FileGroupName
    ,[rows] AS NumberOfRows
    ,au.total_pages AS TotalPages
FROM sys.partitions AS p
    INNER JOIN sys.tables AS t ON t.[object_id] = p.[object_id]
    INNER JOIN sys.schemas AS sc ON sc.[schema_id] = t.[schema_id]
    LEFT JOIN sys.indexes AS i ON p.[object_id] = i.[object_id]
        AND p.index_id = i.index_id
    LEFT JOIN sys.destination_data_spaces AS dds ON 
        dds.partition_scheme_id = i.data_space_id
        AND dds.destination_id = p.partition_number
    LEFT JOIN sys.filegroups AS f ON f.data_space_id = i.data_space_id
    LEFT JOIN (
                SELECT container_id, SUM(total_pages) AS total_pages
                FROM sys.allocation_units
                GROUP BY container_id
                ) AS au ON au.container_id = p.partition_id
WHERE i.index_id > 0
ORDER BY SchemaName, ObjectName, IndexName;

This query also returns the filegroup the particular index uses.  If you aren’t currently utilizing multiple filegroups for your database I would strongly recommend investigating that option.

Let me know if you have questions (or requests).  Enjoy…

Several outlets reporting that Windows 7 RC will be available on April 30th to MSDN/Technet subscribers and May 5th for general public.  I’m just happy this is dropping before I go on vacation.  :)

My word, I’m such as geek…back to SQLSaturday prep.

This came up at the SQLSaturday speaker dinner tonight and I had missed Bob Ward’s post about it.  Definitely good news for all those customers on Standard edition. 

The cumulative update for SQL Server 2005 should be arriving in June for SP3.  SQL Server 2008 will be getting CU2 for SP1 in May.

Always cool to see the SQL team get it right.

I would like to thank all the great people that came out today, it was an awesome day.  I really enjoyed meeting a lot of new people and hopefully spreading a little excitement about partitioning and meta data.  A special thanks to all the folks that attended my sessions.

The slide decks and code samples for both of my sessions can be found below.  If you have any questions please contact me directly at my Magenic address or through the Email section of this blog.

SQLSaturday_Apr25_Partitioning&MetaData.zip (7.79 mb)

I’ve been doing quite a bit of modeling lately and needed to go back and verify that I had the various default constraints as I wanted them on my tables.  In this case, I was looking for tables with constraint value using the getdate() system function.

Here is the T-SQL to get at the default constraint values:

SELECT SCHEMA_NAME(t.[schema_id]) AS SchemaName
    ,t.name AS TableName
    ,c.name AS ColumnName
    ,dc.[definition]
FROM sys.tables AS t
    INNER JOIN sys.columns AS c 
        ON c.[object_id] = t.[object_id]
    INNER JOIN sys.default_constraints AS dc 
        ON dc.[parent_object_id] = c.[object_id]
        AND dc.parent_column_id = c.column_id
WHERE t.is_ms_shipped = 0
    AND dc.definition = '(GETDATE())'
ORDER BY SchemaName
    ,TableName
    ,ColumnName;

As an aside, I’ve been using the super handy new datetimeoffset data type everywhere I had normally used datetime.  The default constraint you would want to implement with datetimeoffset is sysdatetimeoffset() instead of getdate().

I really wish this had been implemented back in the 2005 product, I would have written a lot less code to do time zone to time zone conversion.  Oh well, progress is good whenever it comes.

Enjoy!

I was helping a colleague out with some data migration today and got started discussing methods of finding data that would cause problems.  In today’s case he needed to verify the phone numbers contained in a database table didn’t contain a couple values that would break later application formatting.  Here is a simple example of the data (note the row constructor if trying this in pre SQL 2008 versions):

CREATE TABLE #PhoneNumbers
(
PhoneNumber    nvarchar(30)
);
INSERT #PhoneNumbers (PhoneNumber)
VALUES ('11122223333')
    ,('111.222.3333')
    ,('111-222-3333')
    ,('111 222 3333')
    ,('111/222/3333');

Now we could simply apply this query to test for any character data in our phone number:

SELECT *
FROM #PhoneNumbers
WHERE ISNUMERIC(PhoneNumber) = 1;

But this doesn’t really get at what we want.  In this case, we only care about certain characters.  To our aid comes the trusty PATINDEX() function, allowing us to write this query:

SELECT *
FROM #PhoneNumbers
WHERE PATINDEX('%[. /]%', PhoneNumber) > 0;

This allows us to ignore phone numbers with “-“ as they don’t offend our application’s sensibilities.  The same query can be used later with a REPLACE() as the basis for an update to remove the invalid characters.  Good times all around.

I will be speaking at SQLSaturday on the 25th at the Microsoft office in Alpharetta.  I’m doing two sessions, A Lap Around SQL Server 2008 Partitioning and Solving Real World Problems With Meta Data.  The partitioning talk I’ve done a couple times in the past but the meta data one is new.  If you can’t get in next Saturday (there is a waiting list it seems, wowza) I will have slides and code samples posted soon after.  Hope to see you there!

The Data Platform Insider has the good news that SP1 is available.  Go get your download on…

I helped a fellow Magenicon with this detail today and thought it might be helpful to others.  Note, this will only work in instances that are SQL 2005 or greater.

SELECT SCHEMA_NAME(o.[schema_id]) AS SchemaName
    ,o.name AS ObjectName
    ,REPLACE(o.type_desc, '_', ' ') AS ObjectType
    ,ap.parameter_id AS ParameterNumber
    ,ap.name AS ParameterName
    ,CASE
        WHEN t.name IN ( 'char', 'nchar', 'varchar'
                        , 'nvarchar', 'binary', 'image'
                        , 'varbinary' ) THEN t.name 
            + ' (' + CONVERT(varchar(10), ap.max_length) + ')'
        WHEN t.name IN ( 'bigint', 'bit', 'int', 'money'
                        , 'smallint', 'smallmoney', 'tinyint'
                        , 'float', 'real', 'date', 'datetime2'
                        , 'datetime', 'smalldatetime', 'time'
                        , 'cursor', 'hierarchyid', 'sql_variant'
                        , 'table', 'timestamp', 'uniqueidentifier'
                        , 'xml', 'sysname', 'text', 'ntext' ) THEN t.name
        WHEN t.name = 'datetimeoffset' THEN t.name 
            + ' (' + CONVERT(varchar(10), ap.scale) + ')'
        WHEN t.name IN ('decimal', 'numeric') THEN t.name 
            + ' (' + CONVERT(varchar(10), ap.[precision]) 
            + ', ' + CONVERT(varchar(10), ap.scale) + ')'
        ELSE 'Unknown'
    END AS DataType
    ,CASE
        WHEN ap.is_output = 1 THEN 'Yes'
        ELSE 'No'
    END AS IsOutputParameter
    ,COALESCE(ap.default_value, 'N/A') AS DefaultValue
FROM sys.all_parameters AS ap
    INNER JOIN sys.objects AS o ON o.[object_id] = ap.[object_id]
    INNER JOIN sys.types AS t ON t.system_type_id = ap.system_type_id
ORDER BY SchemaName
    ,ObjectName
    ,ParameterNumber;

Bart Duncan has a great post today about the new datetimeoffset feature in SQL Server 2008.  Add it to the growing list of reasons to migrate…