Friday, November 12, 2010

How to see object definition in sql server

Found this neat function today - it shows you the internals of what tables a view is hitting, or the definition of a stored proc (ie sp_who2).  For example running the following will return you the definition of the service broker view:


SELECT object_definition(OBJECT_ID('sys.service_queues'))

Yields:
CREATE VIEW sys.service_queues AS  

SELECT o.name
       ,o.object_id
       ,o.principal_id
       ,o.schema_id
       ,o.parent_object_id
       ,o.type
       ,o.type_desc
       ,o.create_date
       ,o.modify_date
       ,o.is_ms_shipped
       ,o.is_published
       ,o.is_schema_published
       ,convert(smallint, o.property) AS max_readers
       ,convert(nvarchar(776), v.value) COLLATE database_default AS activation_procedure
       ,e.indepid AS execute_as_principal_id
       ,o.is_activation_enabled
       ,o.is_receive_enabled
       ,o.is_enqueue_enabled
       ,o.is_retention_enabled  
FROM sys.objects$ o  
  LEFT JOIN sys.sysobjvalues v
    ON v.valclass = 12 AND v.objid = o.object_id AND v.subobjid = 0 AND v.valnum = 0 -- SVC_QUEUEACTIVPROC  
  LEFT JOIN sys.syssingleobjrefs e
    ON e.depid = o.object_id AND e.class = 22 AND e.depsubid = 0 -- SRC_OBJEXECASOWNER  
WHERE o.type = 'SQ'
 
 Very cool stuff.


Thursday, November 4, 2010

Find out last backup time for all databases

This is merely a re-post of a script done on SQL Authority that I placed on my site so I can easily find it in the future.


If you have not read any of Pinal Dave's blogs (SQL Authority), you should - he posts some very good information and knows a vast amount about SQL and it's inner workings.


 This script will display by database the last time it was backed up.


SELECT sdb.Name AS DatabaseName,
COALESCE(CONVERT(VARCHAR(12), MAX(bus.backup_finish_date), 101),'-') AS LastBackUpTime
FROM sys.sysdatabases sdb
LEFT OUTER JOIN msdb.dbo.backupset bus ON bus.database_name = sdb.name
GROUP BY sdb.Name
Order by 2

Tuesday, November 2, 2010

Multi Column Report in Reporting Services

Reporting Services in all of its awesomeness is lacking in one area- multi-column (or newspaper style) reports.  It can do it, but only certain rendering formats support it.  One way around it in RS 2008+ is to use the oh so handy tablix control and the TSQL row_number() function.  Here’s how it works:
   
      1.  Create a query that returns the information you want.  Include 2 additional columns: Row Number and Column Number.  The purpose of these is to show the x/y coordinates in the tablix group.  The example query I use will pull a list of tables and columns from the information_schema view:
declare @numberOfColumns int = 3

select table_name
      ,column_name
      ,[row_number] = ( ROW_NUMBER() over (partition by table_name order by table_name) - 1 ) / @numberOfColumns
      ,[column_number] = ( ROW_NUMBER() over (partition by table_name order by table_name) - 1) % @numberOfColumns
from INFORMATION_SCHEMA.COLUMNS
     
     2. Create a new report, place a matrix (tablix) on the report body, and create the groups as follows:

3.  For the column title I set the value of the expression to show the column number returned by the query: ="Column " & Fields!column_number.Value


4.  Add some alternating background coloring to make the report more readable : =iif(RunningValue(Fields!table_name.Value,CountDistinct,Nothing) Mod 2, "AliceBlue", "White")



And presto!  When you view your report it will show you a nicely formatted multiple column report.  For fun, you can make the @numberOfColumns a parameter for the report and allow dynamic column counts.