Posted in: SQL

SQL Script to find all SQL Agent Jobs for SQL Reporting Services Subscriptions

Posted on December 18, 2016 by Michael Roma
select 
    job.name as SQLAgentJob,
    subscr.SubscriptionID,
    report.Name, 
    report.Path,    
    subscr.Description,
    subscr.LastRunTime,
    subscr.LastStatus

from 
    Subscriptions subscr
    join
    ReportSchedule sched
    on subscr.SubscriptionID = sched.SubscriptionID
    join
    Catalog report
    on sched.ReportID = report.ItemID
    join
    msdb.dbo.sysjobs job
    on cast(sched.ScheduleID as nvarchar(50)) = job.name

order by 
    report.name

SQL Script to find all Indexes that need to be added

Posted on November 14, 2013 by Michael Roma

This SQL script will show all Table Indexes that need to be added based on query history.

Just need to change database_id=12 to the database you want to check:

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT TOP 50
ROUND (s.avg_total_user_cost * s.avg_user_impact * (s.user_seeks + s.user_scans),0) AS [Total Cost], 
s.avg_user_impact, 
d.statement AS TableName, 
d.equality_columns, 
d.inequality_columns, 
d.included_columns
FROM sys.dm_db_missing_index_groups g
INNER JOIN sys.dm_db_missing_index_group_stats s
ON s.group_handle = g.index_group_handle
INNER JOIN sys.dm_db_missing_index_details d
ON d.index_handle = g.index_handle
where d.database_id = 12
ORDER BY [Total Cost] DESC

 

SQL helper script to generate properties for an Entity Framework model

Posted on October 16, 2013 by Michael Roma

This post shows a T-SQL script that can be used to generate the properties for a class that correspond to to a field in a database table. This can be used with Entity Framework.

T-SQL script to generate the properties:

-- define the table to return back
declare @t nvarchar(100); set @t = 'lookup_site_url'

-- return back the properties
select 	
	'public ' + t + ' ' + n + ' { get; set; }'	
	
from (
	select c.name as n, 

		-- convert t-sql type to .net c# type
		case c.user_type_id
			when 127 then 'long'
			when 167 then 'string'
			when 35 then 'string'
			when 231 then 'string'
			when 241 then 'string'	
			when 56 then 'int'
			when 61 then 'DateTime'
			when 104 then 'bool'
			when 62 then 'double'
			when 60 then 'decimal'
			when 106 then 'decimal'
			when 36 then 'Guid'
			else null
		end as t, user_type_id, max_length	 
	
	from 
		sys.columns c, sys.tables t 
	where 
		c.object_id = t.object_id and t.name = @t
) d

C# Helper functions to map a DataTable or DataRow to a class object

Posted on March 30, 2013 by Michael Roma

The following methods will allow you to map your DataTable, DataRow results to a class object.

Here are the helper functions:

// function that set the given object from the given data row
public static void SetItemFromRow(T item, DataRow row)
    where T : new()
{
    // go through each column
    foreach (DataColumn c in row.Table.Columns)
    {
        // find the property for the column
        PropertyInfo p = item.GetType().GetProperty(c.ColumnName);

        // if exists, set the value
        if (p != null && row[c] != DBNull.Value)
        {
            p.SetValue(item, row[c], null);
        }
    }
}

// function that creates an object from the given data row
public static T CreateItemFromRow(DataRow row)
    where T : new()
{
    // create a new object
    T item = new T();

    // set the item
    SetItemFromRow(item, row);

    // return 
    return item;
}

// function that creates a list of an object from the given data table
public static List CreateListFromTable(DataTable tbl)
    where T : new()
{
    // define return list
    List lst = new List();

    // go through each row
    foreach (DataRow r in tbl.Rows)
    {
        // add to the list
        lst.Add(CreateItemFromRow(r));
    }

    // return the list
    return lst;
}

Here are examples on using these helper methods to map to your Customer class:

// get the list of customers in a data table
DataTable customerTable = GetCustomerTable(); // select * from customer

// map to a list of customers
List customerList = CreateListFromTable(customerTable);


// get a single customer in a data row
DataRow customerRow = GetSingleCustomer(3); // select * from customer where id = 3

// map to a customer
Customer customer = CreateItemFromRow(customerRow);

How to join a table-valued function in T-SQL

Posted on January 30, 2013 by Michael Roma

Lets say you have the following table called item with columns (itemnum, categories) and a table-valued function called split that splits the given data by comma to rows.

Example table: itemnum categories A catg1, catg2 B catg2 C catg1, catg3, catg4

Here is a query that will join in those results:

select 
    i.id_item, c.Data as catg
from 
    item i      

    cross apply
    dbo.split(i.categories, ',') c


Resulting query: itemnum catg A catg1 A catg2 B catg2 C catg1 C catg3 C catg4