ronmichael

select
	dbo.sysobjects.name TableName, 
	dbo.syscolumns.name ColumnName, 
	dbo.systypes.name as Type
from dbo.sysobjects 
join dbo.syscolumns on dbo.syscolumns.id = dbo.sysobjects.id 
join dbo.systypes on dbo.systypes.xtype = dbo.syscolumns.xtype
where dbo.systypes.name != 'sysname'
 

ronmichael

select type_desc, name
from sys.objects o
left join sys.database_permissions p on p.major_id=o.object_id
where p.class is null
and type_desc not in ('SYSTEM_TABLE','INTERNAL_TABLE','SERVICE_QUEUE','SQL_TRIGGER','TYPE_TABLE')
and type_desc not like '%CONSTRAINT%'
order by type_desc, name

ronmichael

/*
   if you have a table of rows with parent rows, this will return one row for each row and each of its parents.
   thanks to http://stackoverflow.com/questions/13487006/use-sql-server-cte-to-return-all-parent-records.
*/

with items(uniqueid,parentid) as (

	select uniqueid, uniqueid
	from equipment

ronmichael

/*
	Formats a typical US phone number, including an extension (if preceeded by something like x, ex, ext, etc).
	Crude but adequate.
*/

String.prototype.formatPhone = function () {
    
    var phone = this;

    if (!phone) return "";

ronmichael

SELECT 
	files.physical_name, 
	files.name, 
	stats.num_of_writes, (1.0 * stats.io_stall_write_ms / stats.num_of_writes) avg_write_stall_ms,
	stats.num_of_reads, (1.0 * stats.io_stall_read_ms / stats.num_of_reads) avg_read_stall_ms
FROM sys.dm_io_virtual_file_stats(2, NULL) as stats
JOIN master.sys.master_files AS files ON stats.database_id = files.database_id AND stats.file_id = files.file_id
WHERE files.type_desc = 'ROWS'

/*

ronmichael

declare @personid int = 1900, @start date = '2/24/2014', @stop date ='3/2/2014';


merge people_schedules ps

using (
	select row_number() over ( partition by ps2.eventid, ps2.date order by ps2.uniqueid ) as row, ps2.uniqueid, ps2.personid, ps2.eventid, ps2.date, ps2.servicerequestid, 
		sum(ps3.labortimeregular) LaborTimeTotal,
		from People_Schedules ps2
		join People_Schedules ps3 on ps2.eventid=ps3.eventid and ps2.personid=ps3.personid and ps2.date=ps3.date

ronmichael

select o.type_desc type, o.name
from sys.OBJECTS o
left join sys.sql_expression_dependencies ref on ref.referenced_id = o.object_id
left join sys.OBJECTS o2 on o2.object_id = ref.referencing_id
where o2.name is  null
and o.type_desc not in ('SYSTEM_TABLE','SQL_TRIGGER','INTERNAL_TABLE','SERVICE_QUEUE','TYPE_TABLE')
and o.type_desc not like '%CONSTRAINT%'
order by o.type_desc, o.name

ronmichael

declare @x xml = '<data UniqueID="1" Name="Bob"/><data UniqueID="2" Name="Joan"/>'

select * from dbo.fnu_XML_ToTable(@x)

ronmichael

CREATE TRIGGER [access_trigger]
ON ALL SERVER 
with execute as 'sa' -- needed to query sys.dm_exec_connections table
FOR LOGON

AS
BEGIN

	if original_login() not in ('superadmin', 'anothersuperadmin', 'mydomain\admin')
	and exists (		

ronmichael

// http://stackoverflow.com/questions/6930578/get-cursor-or-text-position-in-pixels-for-input-element/7948715#7948715


function getTextBoundingRect(input, selectionStart, selectionEnd, debug) {
    // Basic parameter validation
    if (!input || !('value' in input)) return input;
    if (typeof selectionStart == "string") selectionStart = parseFloat(selectionStart);
    if (typeof selectionStart != "number" || isNaN(selectionStart)) {
        selectionStart = 0;
    }