MDX query to list all dimension attributes

/*

http://msdn.microsoft.com/en-us/library/ms126309.aspx
http://ssas-wiki.com/w/Articles#DMV_.28Dynamic_Management_View.29
http://dwbi1.wordpress.com/2010/01/01/ssas-dmv-dynamic-management-view/
http://www.ssas-info.com/analysis-services-faq/80-ssas-2008-dmvs/1342-analysis-services-2008-systemmdschema-dmvs-

*/


select
Property_Name
,Property_Caption
,*
from
$SYSTEM.MDSCHEMA_PROPERTIES
where
Property_Type = 1
and Cube_Name = 'The name of your cube'

drop empty table

EXEC sp_msforeachtable 'IF NOT EXISTS(SELECT TOP 1 1 FROM ?) BEGIN PRINT ''drop table ?'' end'

Execute a series of SQL commands

CREATE TYPE [dbo].[ExecSqlStatementsTableType] AS TABLE(
[RowId] [int] IDENTITY(1,1) NOT NULL,
[SqlCode] [nvarchar](max) NOT NULL
)

go

/*

declare @tblSqlCode dbo.ExecSqlStatementsTableType

insert into @tblSqlCode(SqlCode)

values('print ''task 1'''), ('print ''task 2''')

exec util.usp_ExecSqlStatements @TableSqlCode=@tblSqlCode

*/

CREATE proc [util].[usp_ExecSqlStatements](

@TableSqlCode dbo.ExecSqlStatementsTableType READONLY

,@verbose bit = 0

,@ExecSql bit = 1

)

as

-- ************************************************************************************************

-- ************************************************************************************************

set nocount on

-- ************************************************************************************************

-- ************************************************************************************************

-- ************************************************************************************************

declare @MaxRows int

declare @row int

declare @sql nvarchar(max)

-- ************************************************************************************************

-- ************************************************************************************************

-- ************************************************************************************************

select

@row = min(RowId)

,@MaxRows = max(RowId)

from

@TableSqlCode

-- ************************************************************************************************

while @row <= @MaxRows

begin

select

@sql = SqlCode

from

@TableSqlCode

where

RowId = @row

if @sql is not null

begin 

if @verbose=1

begin

print @sql

end

if @ExecSql=1

begin

exec sp_executesql @sql

end

end

set @row = @row +1

end

Good collection of SSRS tips

http://www.sqlmusings.com/2010/06/20/ssrs-tidbits-%E2%80%93-common-tips-tricks-debugging-and-other-references-part-1/ 

Selecting Rows Randomly from a Large Table

http://msdn.microsoft.com/en-us/library/cc441928.aspx

split strings into rows of characters

ALTER FUNCTION [dbo].[fn_CharSplit4K](
@pString NVARCHAR(4000)
)
--WARNING!!! DO NOT USE MAX DATA-TYPES HERE!  IT WILL KILL PERFORMANCE!
-- From the article: http://www.sqlservercentral.com/articles/Tally+Table/72993/
RETURNS TABLE WITH SCHEMABINDING AS
RETURN

select
row_number() over(order by tl.N) as ItemNumber
,substring(@pString, tl.N, 1) as Item
from
dbo.TallyLarge tl with(nolock)
where
tl.N <= len(@pString)

SQL abbreviated name from camel casing

/*

-- returns 'mtc' since the uppercase letters are M, T, C
select misc.fn_AbrvNameFromCamelCasing('MyTestCase')

*/
alter function misc.fn_AbrvNameFromCamelCasing(
@pString NVARCHAR(4000)
)
returns NVARCHAR(max)
as
begin

return (
SELECT
lower((
SELECT [text()]= ISNULL(q.Item, '')
FROM
(
select
cs.Item
from
dbo.fn_CharSplit4K(@pString) cs
where
cs.Item = upper(cs.Item) Collate SQL_Latin1_General_CP1_CS_AS
) q
FOR XML PATH('')
)) AS AbrvName
)
end

Foreign Keys without Indexes

http://www.sqlservercentral.com/scripts/Administration/68357/

/*
*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=
Util_FKsWithoutIndexes
By Jesse Roberge - YeshuaAgapao@Yahoo.com

Searches for foreign key constraints that don't have fully matching indexes.
The best partial matching indexes are outputted with MatchCounts and column comparisons
Generates a CREATE INDEX template for each foreign-key with no matching index or a partial-matching index.
Customize as needed (add includes, if you want it clustered, or if it should be a part of the primary key, or if you want to merge with another index)
FKs missing full matching indexes can severely hurt the performance of DELETES on the referenced table due to table-scans for checking referential integrity,
as well as SELECTS on the referencing tables where the foreign-key column(s) are in the WHERE or JOIN predicates (This will affect you whether a constraint exists or not).
This only checks the first N columns of the index where N is the number of columns in the foreign key constraint.
Index column order is not verified beyond this (A two-col FK that has 1 matching column in the 2nd col of a 3-col index will be outputted as a partial match)
If your database has no foreign key constraints, then this tool will be worthless to you.
Many databases have partial foreign key constraint coverage. This only works on related tables where constraints are declared.

Required Input Parameters:
None

Optional Input Parameters:
@Delimiter VarChar(1)=’,’

Usage:
EXECUTE dbo.Util_FKsWithoutIndexes

Copyright:
Licensed under the L-GPL - a weak copyleft license - you are permitted to use this as a component of a proprietary database and call this from proprietary software.
Copyleft lets you do anything you want except plagiarize, conceal the source, or prohibit copying & re-distribution of this script/proc.

This program is free software: you can redistribute it and/or modify
    it under the terms of the GNU Lesser General Public License as
    published by the Free Software Foundation, either version 3 of the
    License, or (at your option) any later version.

    This program is distributed in the hope that it will be useful,
    but WITHOUT ANY WARRANTY; without even the implied warranty of
    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
    GNU Lesser General Public License for more details.

    see for the license text.

*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=
*/

ALTER PROCEDURE [dbo].[Util_FKsWithoutIndexes]
@Delimiter VarChar(1)=','
AS

SELECT
--  parentschemas.schema_id AS ReferencingSchemaID
--, parentschemas.name AS ReferencingSchemaName
--,  parentobjects.object_id AS ReferencingTableID
parentobjects.name AS ReferencingTableName
--, referencedschemas.schema_id AS ReferencedSchemaID
--, referencedschemas.name AS ReferencedSchemaName
--, referencedobjects.object_id AS ReferencedTableID
, referencedobjects.name AS ReferencedTableName
, objects.object_id AS FKConstraintID
, objects.name AS FKConstraintName
, FKTotal.FKColumnCount
, FKMatch.index_id AS PartialMatchIndexID
, indexes.name AS PartialMatchIndexName
, FKMatch.IndexColumnMatchCount
, foreign_key_columns.foreign_key_columns
, index_columns.index_columns_key
, index_columns.index_columns_include
, 'CREATE NONCLUSTERED INDEX IX_' + parentobjects.name + '_' + REPLACE(foreign_key_columns.foreign_key_columns, ', ', '_') + ' ON ' + parentschemas.name + '.' + parentobjects.name + ' (' + foreign_key_columns.foreign_key_columns + ')' AS FKIndexCreate
-- 'CREATE NONCLUSTERED INDEX IX__' + parentschemas.name + '_' + parentobjects.name + '__' + REPLACE(foreign_key_columns.foreign_key_columns, ', ', '_') + ' ON ' + parentschemas.name + '.' + parentobjects.name + ' (' + foreign_key_columns.foreign_key_columns + ')' AS FKIndexCreate
FROM
(
SELECT foreign_key_columns.constraint_object_id, MAX(referenced_object_id) AS referenced_object_id, COUNT(*) AS FKColumnCount
FROM sys.foreign_key_columns
GROUP BY foreign_key_columns.constraint_object_id
) AS FKTotal
JOIN sys.objects ON FKTotal.constraint_object_id=objects.object_id
JOIN sys.objects AS parentobjects ON objects.parent_object_id=ParentObjects.object_id
JOIN sys.schemas AS parentschemas ON parentobjects.schema_id=parentschemas.schema_id
JOIN sys.objects AS referencedobjects ON FKTotal.referenced_object_id=referencedObjects.object_id
JOIN sys.schemas AS referencedschemas ON referencedobjects.schema_id=referencedschemas.schema_id
OUTER APPLY (
SELECT constraint_object_id, index_id, index_object_id, IndexColumnMatchCount
FROM
(
SELECT
foreign_key_columns.constraint_object_id, index_columns.index_id, MAX(index_columns.object_id) AS index_object_id,
COUNT(*) AS IndexColumnMatchCount,
ROW_NUMBER() OVER (PARTITION BY foreign_key_columns.constraint_object_id ORDER BY COUNT(*) DESC) AS IndexColumnMatchCountRowNumber
FROM
sys.foreign_key_columns
JOIN sys.index_columns ON
index_columns.object_id=foreign_key_columns.parent_object_id
AND index_columns.column_id=foreign_key_columns.parent_column_id
WHERE
index_columns.is_included_column=0
AND index_columns.key_ordinal<=FKTotal.FKColumnCount
AND FKTotal.constraint_object_id=foreign_key_columns.constraint_object_id
GROUP BY foreign_key_columns.constraint_object_id, index_columns.index_id
) AS FKMatch
WHERE IndexColumnMatchCountRowNumber=1
) AS FKMatch
LEFT OUTER JOIN sys.indexes ON FKMatch.index_object_id=indexes.object_id AND FKMatch.index_id=indexes.index_id
CROSS APPLY (
SELECT
LEFT(index_columns_key, LEN(index_columns_key)-1) AS foreign_key_columns
FROM
(
SELECT
(
SELECT columns.name + @Delimiter + ' '
FROM
sys.foreign_key_columns
JOIN sys.columns ON
foreign_key_columns.parent_column_id=columns.column_id
AND foreign_key_columns.parent_object_id=columns.object_id
WHERE
FKTotal.constraint_object_id=foreign_key_columns.constraint_object_id
ORDER BY constraint_column_id
FOR XML PATH('')
) AS index_columns_key
) AS Index_Columns
) AS foreign_key_columns
CROSS APPLY (
SELECT
LEFT(index_columns_key, LEN(index_columns_key)-1) AS index_columns_key,
LEFT(index_columns_include, LEN(index_columns_include)-1) AS index_columns_include
FROM
(
SELECT
(
SELECT columns.name + @Delimiter + ' '
FROM
sys.index_columns
JOIN sys.columns ON
index_columns.column_id=columns.column_id
AND index_columns.object_id=columns.object_id
WHERE
index_columns.is_included_column=0
AND FKMatch.index_object_id=index_columns.object_id
AND FKMatch.index_id=index_columns.index_id
ORDER BY key_ordinal
FOR XML PATH('')
) AS index_columns_key,
(
SELECT sys.columns.name + @Delimiter + ' '
FROM
sys.index_columns
JOIN sys.columns ON
sys.index_columns.column_id=sys.columns.column_id
AND sys.index_columns.object_id=sys.columns.object_id
WHERE
sys.index_columns.is_included_column=1
AND sys.indexes.object_id=sys.index_columns.object_id
AND sys.indexes.index_id=sys.index_columns.index_id
ORDER BY index_column_id
FOR XML PATH('')
) AS index_columns_include
) AS Index_Columns
) AS Index_Columns
WHERE FKMatch.IndexColumnMatchCount IS NULL OR FKTotal.FKColumnCount<>FKMatch.IndexColumnMatchCount
ORDER BY
IndexColumnMatchCount DESC, FKColumnCount DESC,
parentobjects.name, objects.name

Locating users within a DB

exec sp_MSforeachdb 'select ''?'' as Db, * from ?.sys.sysusers where name like ''%SomeUser%'''

Delete duplicates

delete from a
from
dbo.tableWithDupes a
join (
select
ColPrimaryKey
,row_number() over(
partition by AccountId, Col2, Col3 -- these are the unique columns
order by AccountId -- use this to give one record priority over another
) DupeCount
from
dbo.tableWithDupes
) q
on a.ColPrimaryKey = q.ColPrimaryKey
where
q.DupeCount > 1

SET STATISTICS IO ON
SET STATISTICS TIME ON
SET STATISTICS PROFILE ON
SET STATISTICS XML ON

List all identity columns

SELECT        
c.TABLE_CATALOG
,c.TABLE_SCHEMA
,t.TABLE_NAME
,c.COLUMN_NAME
FROM        
INFORMATION_SCHEMA.COLUMNS AS c JOIN
INFORMATION_SCHEMA.TABLES AS t
ON t.TABLE_NAME = c.TABLE_NAME
WHERE        
COLUMNPROPERTY(OBJECT_ID(c.TABLE_NAME), c.COLUMN_NAME, 'IsIdentity') = 1 AND
t.TABLE_TYPE = 'Base Table' AND
t.TABLE_NAME NOT LIKE 'dt%' AND
t.TABLE_NAME NOT LIKE 'MS%' AND
t.TABLE_NAME NOT LIKE 'syncobj_%'

Convert hex string to bigint

from http://www.idilks.com/mssql/md5







-- select md5 as a hex string (regular string)

SELECT master.sys.fn_varbintohexsubstring(0, HashBytes('MD5', 'SQL Centroid'), 1, 16)

-- convert a hex string from a string to varbinary(16)

SELECT CAST('' as xml).value('xs:hexBinary("2655a2b5ef3531c4fd330c9ab409afa9")', 'varbinary(16)')

-- convert a hex string from a variable to varbinary(16)

DECLARE @thestring CHAR(32)

SELECT @thestring = '2655a2b5ef3531c4fd330c9ab409afa9'

SELECT CAST('' AS XML).value('xs:hexBinary(sql:variable("@thestring"))', 'varbinary(16)')

-- convert a hex string from a varchar column to varbinary(16)

DECLARE @TABLEA TABLE (tID int IDENTITY(1,1) PRIMARY KEY, tst varchar(200))

INSERT INTO @TABLEA (tst) VALUES ('2655a2b5ef3531c4fd330c9ab409afa9')

SELECT CAST('' AS XML).value('xs:hexBinary(sql:column("t.tst"))', 'varbinary(16)')

FROM @TABLEA t




SELECT top 10

cast(CAST('' AS XML).value('xs:hexBinary(sql:column("f.crcString"))', 'varbinary(8)') as bigint)

,f.crcString

FROM 

filecompare f

delete empty folders

find . -empty -type d -delete

Remove "nul" file in windows

Original article can be found here

You need to specify the full path del "\\?\c:\folder\nul"

list of all the tables in SSAS

To get a list of all the tables in SSAS, execute this in an MDX window:
SELECT * FROM $system.dbschema_tables
where Table_type = 'schema'

List compressed SQL tables

select 
 '[' + s.name + '].[' + o.name + ']' as TblName
 ,p.data_compression_desc
from 
 sys.partitions p
 join sys.objects o
  on p.object_id = o.object_id
 join sys.schemas s
  on o.schema_id = s.schema_id
where
 p.data_compression != 0

THE EVOLUTION OF THE WEB

Great visualization. Doesn't render correctly in IE. You'll need to use another browser like Safari.

Get the user list for an Active Directory group

dsquery group -samid SecurityGroup | dsget group -members -expand | findstr /i ",OU=UserAccounts," | dsget user -samid > c:\SecurityGroup_userlist.txt

Using SQL to generate cygwin curl command line to render all SSRS reports to a file on disk

use [ReportServer]
go

-- http://server/ReportServer?REPORT_PATH&rs:Command=Render&rs:Format=PDF&myparam1=Value1&myparam2=Value2
-- http://server/ReportServer?REPORT_PATH&rs:Command=Render&rs:Format=CSV&rc:FieldDelimiter=%09

SELECT
	'curl --ntlm -u "ntlmDomain\ntlmUserName:Password" "'
	+ replace(replace('http://server/ReportServer?REPORT_PATH&rs:Command=Render&rs:Format=CSV&rc:FieldDelimiter=%09', 'REPORT_PATH', c.Path),' ', '%20')
	+ '" > "' + replace(c.Path, '/', '_') + '.txt"'
FROM 
	[dbo].[Catalog] c
WHERE 
	Type = 2
	--and Path like '/BetaSite/%'

Programatically rendering SSRS reports and writing them to a file

http://geekswithblogs.net/bsherwin/archive/2007/04/29/112094.aspx
http://bytes.com/topic/sql-server/answers/728883-using-rs-exe-export-report-command-line-urgent
http://msdn.microsoft.com/en-us/library/ms162839.aspx
http://weblogs.sqlteam.com/tarad/archive/2005/01/05/3944.aspx
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=110451

SSRS rendering



http://MYSERVER/ReportServer?MyReport&rs:Command=Render&rs:Format=CSV&rc:FieldDelimiter=%09

http://MYSERVER/ReportServer?MyReport&rs:Command=Render&rs:Format=PDF&myparam1=Value1&myparam2=Value2



SQL Server: How to Figure out Peak/Off-Peak Hours of Production Databases

Bubble Heatmp in SSRS

selecting data from Excel tables using VBA

'select an entire column (data only)
s.Range("Table1[Column2]").Select
'select an entire column (data plus header)
s.Range("Table1[[#All],[Column1]]").Select
'select entire data section of table
s.Range("Table1").Select
'select entire table
s.Range("Table1[#All]").Select

Misc queries

sp_who2
go
xp_cmdshell 'tasklist /FI "IMAGENAME eq Robocopy.exe"'
go
sp_MSforeachtable 'print ''select ''''?'''' as dbn, COUNT(*) cnt, CHECKSUM_AGG(checksum(*)) crc from db1.? (nolock) union all'''
go
sp_MSforeachtable 'print ''select ''''?'''' as dbn, COUNT(*) cnt, CHECKSUM_AGG(checksum(*)) crc from db2.? (nolock) union all'''

Returning the Top X row for each group by By Dave Ballantyne, 2010/12/06

IF OBJECT_ID('tempdb..#RunnersBig') IS NOT NULL drop table #RunnersBig 
go 
Create Table #RunnersBig 
( 
RunnerId integer identity , 
Time integer not null, 
Age integer not null 
) 
go 
insert into #runnersbig ( Time , Age ) 
select top 1000000 ABS ( checksum ( newid ()))% 1000 , 
ABS ( checksum ( newid ()))% 99 
from sys . columns a cross join sys . columns b cross join sys . columns c 
go 
create index idxrunnersbig on #runnersbig ( age , time ) include ( runnerid ) 

with cteN 
as 
( 
select number from master .. spt_values 
where type = 'p' and number between 0 and 100 
) 
Select * 
from cteN cross apply ( Select top ( 2 ) * from #RunnersBig where #RunnersBig . Age = cteN . number order by Time ) as runners 
order by cteN . number , runners . Time 

SQL finding Gaps and Islands

http://www.manning.com/nielsen/SampleChapter5.pdf
or
http://www.manning.com/nielsen/nielsenMEAP_freeCh5.pdf
or
http://www.sql.co.il/books/insidetsql2005/Inside%20Microsoft%20SQL%20Server%202005%20T-SQL%20Querying%20(0-7356-2313-9)%20-%20Chapter%2006%20-%20Aggregating%20and%20Pivoting%20Data.pdf

Rethrow SQL error

Not very interesting, but I use it all the time.

BEGIN TRY

 begin tran

    -- RAISERROR with severity 11-19 will cause execution to 
    -- jump to the CATCH block.
    RAISERROR ('Error raised in TRY block.', -- Message text.
               16, -- Severity.
               1 -- State.
               );
               
 commit tran
END TRY
BEGIN CATCH
    DECLARE @ErrorMessage NVARCHAR(4000);
    DECLARE @ErrorSeverity INT;
    DECLARE @ErrorState INT;

    SELECT 
        @ErrorMessage = ERROR_MESSAGE(),
        @ErrorSeverity = ERROR_SEVERITY(),
        @ErrorState = ERROR_STATE();

 rollback tran

    -- Use RAISERROR inside the CATCH block to return error
    -- information about the original error that caused
    -- execution to jump to the CATCH block.
    RAISERROR (@ErrorMessage, -- Message text.
               @ErrorSeverity, -- Severity.
               @ErrorState -- State.
               );
END CATCH;

Concatenate rows into a single column

From web site http://blogs.technet.com/b/wardpond/archive/2008/03/13/database-programming-the-string-concatenation-xml-trick.aspx


create table Parent
(
ParentID INT
,ParentString VARCHAR(100)
)

INSERT Parent VALUES (1, 'Parent 1 String')
INSERT Parent VALUES (2, 'Parent 2 String')
INSERT Parent VALUES (3, 'Parent 3 String')


SELECT Parent.ParentString
FROM Parent

-- PERFORM THE TRICK
-- PIVOT Parent VALUES INTO 1 COLUMN FOR 1 BASE ROW
SELECT STUFF(( SELECT [text()]= ',' + ISNULL(Parent.ParentString, '') + ''
FROM Parent
ORDER BY Parent.ParentString
FOR XML PATH('')), 1,1, '') AS Parent_CSV


Undocumented SQL stored procedures


exec sp_help 'sp_checknames'
exec sp_help 'sp_columns_rowset'
exec sp_help 'sp_enumoledbdatasources'
exec sp_help 'sp_fixindex'
exec sp_help 'sp_gettypestring'
exec sp_help 'sp_MS_marksystemobject'
exec sp_help 'sp_MSaddguidcolumn'
exec sp_help 'sp_MSaddguidindex'
exec sp_help 'sp_MSaddlogin_implicit_ntlogin'
exec sp_help 'sp_MSadduser_implicit_ntlogin'
exec sp_help 'sp_MScheck_uid_owns_anything'
exec sp_help 'sp_MSdbuseraccess'
exec sp_help 'sp_MSdbuserpriv'
exec sp_help 'sp_msdependencies'
exec sp_help 'sp_MSdrop_object'
exec sp_help 'sp_MSforeachdb'
exec sp_help 'sp_MSforeachtable'
exec sp_help 'sp_MSget_qualified_name'
exec sp_help 'sp_MSgettools_path'
exec sp_help 'sp_MSgetversion'
exec sp_help 'sp_MSguidtostr'
exec sp_help 'sp_MShelpcolumns'
exec sp_help 'sp_MShelpindex'
exec sp_help 'sp_MShelptype'
exec sp_help 'sp_MSindexspace'
exec sp_help 'sp_MSis_pk_col'
exec sp_help 'sp_MSkilldb'
exec sp_help 'sp_MSloginmappings'
exec sp_help 'sp_MStablekeys'
exec sp_help 'sp_MStablerefs'
exec sp_help 'sp_MStablespace'
exec sp_help 'sp_MSunc_to_drive'
exec sp_help 'sp_MSuniquecolname'
exec sp_help 'sp_MSuniquename'
exec sp_help 'sp_MSuniqueobjectname'
exec sp_help 'sp_MSuniquetempname'
exec sp_help 'sp_tempdbspace'
exec sp_help 'sp_who2'
exec sp_help 'xp_delete_file'
exec sp_help 'xp_dirtree'
exec sp_help 'xp_enum_oledb_providers'
exec sp_help 'xp_enumcodepages'
exec sp_help 'xp_enumdsn'
exec sp_help 'xp_enumerrorlogs'
exec sp_help 'xp_enumgroups'
exec sp_help 'xp_fileexist'
exec sp_help 'xp_fixeddrives'
exec sp_help 'xp_get_MAPI_default_profile'
exec sp_help 'xp_get_MAPI_profiles'
exec sp_help 'xp_getnetname'
exec sp_help 'xp_qv'
exec sp_help 'xp_readerrorlog'
exec sp_help 'xp_regaddmultistring'
exec sp_help 'xp_regdeletekey'
exec sp_help 'xp_regdeletevalue'
exec sp_help 'xp_regenumvalues'
exec sp_help 'xp_regread'
exec sp_help 'xp_regremovemultistring'
exec sp_help 'xp_regwrite'
exec sp_help 'xp_subdirs'
exec sp_help 'xp_varbintohexstr'


Automatically add Foreign Keys

 
declare @code nvarchar(4000)
declare @template nvarchar(4000)
declare @PRIM_TABLE_SCHEMA sysname
declare @PRIM_TABLE_NAME sysname
declare @PRIM_COLUMN_NAME sysname
declare @CHILD_TABLE_SCHEMA sysname
declare @CHILD_TABLE_NAME sysname
declare @CHILD_COLUMN_NAME sysname
declare @rowid bigint
declare @maxrows bigint
declare @guid uniqueidentifier
 
set @template = 
'ALTER TABLE <<CHILD_TABLE_SCHEMA>>.<<CHILD_TABLE_NAME>> ADD CONSTRAINT
 FK_<<CHILD_TABLE_NAME>>_<<PRIM_TABLE_NAME>>_<<GUID>> FOREIGN KEY
 (
 <<CHILD_COLUMN_NAME>>
 ) REFERENCES <<PRIM_TABLE_SCHEMA>>.<<PRIM_TABLE_NAME>>
 (
 <<PRIM_COLUMN_NAME>>
 ) ON UPDATE  NO ACTION 
  ON DELETE  NO ACTION'
 
 
-- *******************************************************************************************
-- *******************************************************************************************
-- *******************************************************************************************
-- *******************************************************************************************
if OBJECT_ID('tempdb..#primkeys') is not null
 drop table #primkeys
 
 
select distinct 
 col.TABLE_SCHEMA
 ,col.TABLE_NAME
 ,col.COLUMN_NAME
into #primkeys
from 
 INFORMATION_SCHEMA.TABLES t
 JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS con
  on con.TABLE_SCHEMA = t.TABLE_SCHEMA 
   and con.TABLE_NAME = t.TABLE_NAME 
            AND con.CONSTRAINT_TYPE = 'PRIMARY KEY'
 join INFORMATION_SCHEMA.KEY_COLUMN_USAGE u
  on con.CONSTRAINT_SCHEMA = u.CONSTRAINT_SCHEMA 
   and con.CONSTRAINT_NAME = u.CONSTRAINT_NAME 
 join INFORMATION_SCHEMA.COLUMNS col
  on u.TABLE_SCHEMA = col.TABLE_SCHEMA
   and u.TABLE_NAME = col.TABLE_NAME
   and u.COLUMN_NAME = col.COLUMN_NAME
where
 t.TABLE_TYPE = 'base table'
 and t.TABLE_NAME != 'sysdiagrams'
 
 
-- *******************************************************************************************
-- *******************************************************************************************
-- *******************************************************************************************
-- *******************************************************************************************
if OBJECT_ID('tempdb..#NonKeyColumns') is not null
 drop table #NonKeyColumns
 
 
select distinct 
 col.TABLE_SCHEMA
 ,col.TABLE_NAME
 ,col.COLUMN_NAME
into #NonKeyColumns
from 
 INFORMATION_SCHEMA.TABLES t
 join INFORMATION_SCHEMA.COLUMNS col
  on t.TABLE_SCHEMA = col.TABLE_SCHEMA
   and t.TABLE_NAME = col.TABLE_NAME
where
 t.TABLE_TYPE = 'base table'
 and t.TABLE_NAME != 'sysdiagrams'
 
except
 
select
 *
from
 #primkeys
 
 
 
-- *******************************************************************************************
-- *******************************************************************************************
-- *******************************************************************************************
-- *******************************************************************************************
if OBJECT_ID('tempdb..#colMappings') is not null
 drop table #colMappings
 
 
select
 ROW_NUMBER() over(order by p.TABLE_SCHEMA, p.TABLE_NAME, p.COLUMN_NAME,
  n.TABLE_SCHEMA, n.TABLE_NAME, n.COLUMN_NAME) as  RowID
 ,p.TABLE_SCHEMA as PRIM_TABLE_SCHEMA
 ,p.TABLE_NAME as PRIM_TABLE_NAME
 ,p.COLUMN_NAME as PRIM_COLUMN_NAME
 ,n.TABLE_SCHEMA as CHILD_TABLE_SCHEMA
 ,n.TABLE_NAME as CHILD_TABLE_NAME
 ,n.COLUMN_NAME as CHILD_COLUMN_NAME
into #colMappings
from
 #primkeys p
 join #NonKeyColumns n
  on (
   p.TABLE_SCHEMA != n.TABLE_SCHEMA
   or p.TABLE_NAME != n.TABLE_NAME
   )
   and p.COLUMN_NAME = n.COLUMN_NAME
 
 
 
-- *******************************************************************************************
-- *******************************************************************************************
-- *******************************************************************************************
-- *******************************************************************************************
select 
 @rowid = 1
 ,@maxrows = MAX(RowID)
from
 #colMappings
 
 
while (@rowid <= @maxrows)
begin
 
 select
  @PRIM_TABLE_SCHEMA = PRIM_TABLE_SCHEMA
  ,@PRIM_TABLE_NAME = PRIM_TABLE_NAME
  ,@PRIM_COLUMN_NAME = PRIM_COLUMN_NAME
  ,@CHILD_TABLE_SCHEMA = CHILD_TABLE_SCHEMA
  ,@CHILD_TABLE_NAME = CHILD_TABLE_NAME
  ,@CHILD_COLUMN_NAME = CHILD_COLUMN_NAME
  ,@guid = NEWID()
 from
  #colMappings m
 where
  m.RowID = @rowid
 
 
 set @code = replace(@template, '<<PRIM_TABLE_SCHEMA>>', @PRIM_TABLE_SCHEMA)
 set @code = replace(@code, '<<PRIM_TABLE_NAME>>', @PRIM_TABLE_NAME)
 set @code = replace(@code, '<<PRIM_COLUMN_NAME>>', @PRIM_COLUMN_NAME)
 set @code = replace(@code, '<<CHILD_TABLE_SCHEMA>>', @CHILD_TABLE_SCHEMA)
 set @code = replace(@code, '<<CHILD_TABLE_NAME>>', @CHILD_TABLE_NAME)
 set @code = replace(@code, '<<CHILD_COLUMN_NAME>>', @CHILD_COLUMN_NAME)
 set @code = replace(@code, '<<GUID>>', replace(@guid, '-', ''))
 
 
 print @code
 
 --exec sp_executesql @code
 
 set @rowid = @rowid +1
end
 
 

WatiN Web Test and Web Automation tool

WatiN is an awesome web test and web automation tool. I've been using it to automate the montly uploading of data to a site. This was the best IE solution I could find, and it is fantastic. Selenium & Firefox are another great tool combination. I really like Selenium but the coding of the web site wouldn't work with Firefox.

http://watin.sourceforge.net/


In case WatiN doesn't work with a particular dialog, be sure to check out the methods CanHandleDialog(Window window). WatiN did not originally work with FileUpload element of a particular web site. It worked with several other web site using various FileUpload elements. The solution was to modify the method FileUploadDialogHandler.CanHandleDialog(Window window).

  public override bool CanHandleDialog(Window window)
  {
            return (window.StyleInHex == "96CC20C4") || (window.StyleInHex == "96CC02C4")
                || (window.StyleInHex == "97CC02C4") || (window.StyleInHex == "97CC02C4");
  }

For some reason the style for the upload file dialog was different. I'm not sure if this was caused by a newer browser version, web site, or Windows 7. But once I added the new styles it worked fine.

In summary an awesome tool!

Uninstall applications through the registry

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objTextFile = objFSO.CreateTextFile("UninstallAppList.tsv", True)
Set objWMIService = GetObject("winmgmts:{impersonationLevel=impersonate}!\\.\root\cimv2")

objTextFile.WriteLine "DisplayName" & vbtab & _
    "InstallDate" & vbtab & "ProdId" & vbtab & _
    "Publisher" & vbtab & "Version" & vbtab & "32/64Bits"

'add a where clause
Set colSoftware = objWMIService.ExecQuery("Select * from Win32Reg_AddRemovePrograms")
For Each objSoftware in colSoftware
    objTextFile.WriteLine objSoftware.DisplayName & vbtab & _
        objSoftware.InstallDate & vbtab & _
        objSoftware.ProdId & vbtab & _
        objSoftware.Publisher & vbtab & _
        objSoftware.Version & vbtab & _
        "32"
Next

Set colSoftware = objWMIService.ExecQuery("Select * from Win32Reg_AddRemovePrograms64")
For Each objSoftware in colSoftware
    objTextFile.WriteLine objSoftware.DisplayName & vbtab & _
        objSoftware.InstallDate & vbtab & _
        objSoftware.ProdId & vbtab & _
        objSoftware.Publisher & vbtab & _
        objSoftware.Version & vbtab & _
        "64"
Next


objTextFile.Close

'Use this code to uninstall the application
'Set objShell = CreateObject("Wscript.Shell")
'For Each objSoftware in colSoftware
'    objShell.Run objShell.RegRead("HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\Uninstall\" & objSoftware.ProdId & "\UninstallString"), 1, true
'Next

Refresh Excel file using SSIS

copied from Jessica Moss' blog, in case that blog is ever offline.

http://jessicammoss.blogspot.com/2008/10/manipulating-excel-spreadsheets-in-ssis.html

Next, create a script task in your SSIS package that contains the following code (include your spreadsheet name):

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Imports Microsoft.Office.Interop.Excel

Public Class ScriptMain

Public Sub Main()
Dts.TaskResult = Dts.Results.Success

Dim excel As New Microsoft.Office.Interop.Excel.Application
Dim wb As Microsoft.Office.Interop.Excel.Workbook

wb = excel.Workbooks.Open("C:\\TestExcelSS.xlsx")
wb.RefreshAll()
wb.Save()
wb.Close()

excel.Quit()
Runtime.InteropServices.Marshal.ReleaseComObject(excel)

End Sub

End Class

You'll see error squiggles, but don't worry about them because they will disappear in just a minute. Save and close your package. In your Solution Explorer, right click on the package and select ‘View Code’.

In the resulting XML, change the Build Settings ReferencePath property to:
"C:\Windows\assembly\GAC_MSIL\Microsoft.SqlServer.ScriptTask\9.0.242.0__89845dcd8080cc91\;C:\Windows\assembly\GAC_MSIL\Microsoft.SqlServer.ManagedDTS\9.0.242.0__89845dcd8080cc91\;C:\Windows\assembly\GAC\Microsoft.Office.Interop.Excel\12.0.0.0__71e9bce111e9429c\"

Also change the Build References to include:
Name = "Microsoft.Office.Interop.Excel"
AssemblyName = "Microsoft.Office.Interop.Excel"
/>

Save the XML, and reopen the package. Open the script task and select ‘Save’. This will compile the code, and now you can run your package.

When working with COM references, you can use the script task GUI to add the reference by adding the desired component to the .NET framework folder. I could not find Microsoft.Office.Interop.Excel.dll on my machine to move to the framework folder, which is why we added the reference through the XML.

As Douglas Laudenschlager notes, writing server-side code to access client-side Office is unsupported. Please take these possible problems under advisement and code as necessary. You have been warned. :)

Update (11/12/08): Added last two lines to code to stop Excel process.

Versions: Microsoft Office 2007, SQL Server 2005 SP2

Posted by Jessica M. Moss at 1:11 AM

Download excel file from SSRS using RS

rs -i RSDownloadFile.rss -s http://localhost/reportserver

-------->8 RSDownloadFile.rss 8< --------

Public Sub Main()
Dim fileName as String = "Refreshable_Pivot.xlsx"
Dim strResourcePath as String = "/TestFolder/Refreshable_Pivot.xlsx"

'Dim rs As New ReportingService
Dim myByteArray() As Byte
myByteArray = rs.GetResourceContents(strResourcePath, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet")

' Open a file stream and write out the report
Dim stream As FileStream = File.OpenWrite(fileName)
stream.Write(myByteArray, 0, myByteArray.Length)
stream.Close()
End Sub

uspGenerateSQLAgentJobScheduleReport

create PROC [dbo].[uspGenerateSQLAgentJobScheduleReportWithDuration]
as
/*
Original code by Dr DBA - whomever that is
http://blogs.mssqltips.com/forums/t/977.aspx

modified by Carlos Klapp
*/


select
'Server' = left(@@ServerName,20),
'JobName' = left(S.name,30),
'ScheduleName' = left(ss.name,25),
'Enabled' = CASE (S.enabled)
WHEN 0 THEN 'No'
WHEN 1 THEN 'Yes'
ELSE '??'
END,
'Frequency' = CASE(ss.freq_type)
WHEN 1 THEN 'Once'
WHEN 4 THEN 'Daily'
WHEN 8 THEN
(case when (ss.freq_recurrence_factor > 1)
then 'Every ' + convert(varchar(3),ss.freq_recurrence_factor) + ' Weeks' else 'Weekly' end)
WHEN 16 THEN
(case when (ss.freq_recurrence_factor > 1)
then 'Every ' + convert(varchar(3),ss.freq_recurrence_factor) + ' Months' else 'Monthly' end)
WHEN 32 THEN 'Every ' + convert(varchar(3),ss.freq_recurrence_factor) + ' Months' -- RELATIVE
WHEN 64 THEN 'SQL Startup'
WHEN 128 THEN 'SQL Idle'
ELSE '??'
END,
'Interval' = CASE
WHEN (freq_type = 1) then 'One time only'
WHEN (freq_type = 4 and freq_interval = 1) then 'Every Day'
WHEN (freq_type = 4 and freq_interval > 1) then 'Every ' + convert(varchar(10),freq_interval) + ' Days'
WHEN (freq_type = 8) then (select 'Weekly Schedule' = D1+ D2+D3+D4+D5+D6+D7
from (select ss.schedule_id,
freq_interval,
'D1' = CASE WHEN (freq_interval & 1 <> 0) then 'Sun ' ELSE '' END,
'D2' = CASE WHEN (freq_interval & 2 <> 0) then 'Mon ' ELSE '' END,
'D3' = CASE WHEN (freq_interval & 4 <> 0) then 'Tue ' ELSE '' END,
'D4' = CASE WHEN (freq_interval & 8 <> 0) then 'Wed ' ELSE '' END,
'D5' = CASE WHEN (freq_interval & 16 <> 0) then 'Thu ' ELSE '' END,
'D6' = CASE WHEN (freq_interval & 32 <> 0) then 'Fri ' ELSE '' END,
'D7' = CASE WHEN (freq_interval & 64 <> 0) then 'Sat ' ELSE '' END
from msdb..sysschedules ss
where freq_type = 8
) as F
where schedule_id = sj.schedule_id
)
WHEN (freq_type = 16) then 'Day ' + convert(varchar(2),freq_interval)
WHEN (freq_type = 32) then (select freq_rel + WDAY
from (select ss.schedule_id,
'freq_rel' = CASE(freq_relative_interval)
WHEN 1 then 'First'
WHEN 2 then 'Second'
WHEN 4 then 'Third'
WHEN 8 then 'Fourth'
WHEN 16 then 'Last'
ELSE '??'
END,
'WDAY' = CASE (freq_interval)
WHEN 1 then ' Sun'
WHEN 2 then ' Mon'
WHEN 3 then ' Tue'
WHEN 4 then ' Wed'
WHEN 5 then ' Thu'
WHEN 6 then ' Fri'
WHEN 7 then ' Sat'
WHEN 8 then ' Day'
WHEN 9 then ' Weekday'
WHEN 10 then ' Weekend'
ELSE '??'
END
from msdb..sysschedules ss
where ss.freq_type = 32
) as WS
where WS.schedule_id =ss.schedule_id
)
END,
'Time' = CASE (freq_subday_type)
WHEN 1 then left(stuff((stuff((replicate('0', 6 - len(Active_Start_Time)))+ convert(varchar(6),Active_Start_Time),3,0,':')),6,0,':'),8)
WHEN 2 then 'Every ' + convert(varchar(10),freq_subday_interval) + ' seconds'
WHEN 4 then 'Every ' + convert(varchar(10),freq_subday_interval) + ' minutes'
WHEN 8 then 'Every ' + convert(varchar(10),freq_subday_interval) + ' hours'
ELSE '??'
END,

'Next Run Time' = CASE SJ.next_run_date
WHEN 0 THEN cast('n/a' as char(10))
ELSE convert(char(10), convert(datetime, convert(char(8),SJ.next_run_date)),120) + ' ' + left(stuff((stuff((replicate('0', 6 - len(next_run_time)))+ convert(varchar(6),next_run_time),3,0,':')),6,0,':'),8)
END,
left(qDuration.run_date, 4) + '-' + SUBSTRING(cast(qDuration.run_date as CHAR(8)), 5, 2) + '-' + RIGHT(qDuration.run_date, 2) as run_date,
qDuration.Duration,
qDuration.JobExitStatus
from
msdb.dbo.sysjobschedules SJ
join msdb.dbo.sysjobs S
on S.job_id = SJ.job_id
join msdb.dbo.sysschedules SS
on ss.schedule_id = sj.schedule_id
left join (
select
ROW_NUMBER() over(partition by q.job_id order by q.job_id, q.run_date) as RowId
,q.job_id
,q.run_date
,q.JobExitStatus
,right('0' + rtrim(convert(char(2), q.run_duration_sec / (60 * 60))), 2) + ':' +
right('0' + rtrim(convert(char(2), (q.run_duration_sec / 60) % 60)), 2) + ':' +
right('0' + rtrim(convert(char(2), q.run_duration_sec % 60)),2) as Duration
from
(
select
SH.job_id
,SH.run_date
,case SH.run_status
when 0 then 'Failed'
when 1 then 'Succeeded'
when 2 then 'Retry'
when 3 then 'Canceled'
when 4 then 'In progress'
end as JobExitStatus
,(
(SH.run_duration / 10000) *60*60 + -- hours
((SH.run_duration / 100) % 100) *60 + -- minutes
SH.run_duration % 100 -- seconds
) as run_duration_sec
from
msdb.dbo.sysjobhistory SH
where
SH.step_id = 0
) q
) qDuration
on S.job_id = qDuration.job_id
where
qDuration.RowId between 1 and 10
order by
S.name
,qDuration.run_date desc
,qDuration.Duration




GO

Binding an Excel table to the results of an MDX query

Original article: http://cwebbbi.spaces.live.com/blog/cns!7B84B0F2C239489A!6009.trak

Code Snippets in case the original site goes down.

   1: <xml id=docprops><o:DocumentProperties

   2:   xmlns:o="urn:schemas-microsoft-com:office:office"

   3:   xmlns="http://www.w3.org/TR/REC-html40">

   4:   <o:Name>SSAS Query Test</o:Name>

   5:  </o:DocumentProperties>

   6: </xml><xml id=msodc><odc:OfficeDataConnection

   7:   xmlns:odc="urn:schemas-microsoft-com:office:odc"

   8:   xmlns="http://www.w3.org/TR/REC-html40">

   9:   <odc:Connection odc:Type="OLEDB">

  10:    <odc:ConnectionString>Provider=MSOLAP.4;Integrated Security=SSPI;

  11:     Persist Security Info=True;Data Source=localhost;

  12:     Initial Catalog=Adventure Works DW 2008</odc:ConnectionString>

  13:    <odc:CommandType>MDX</odc:CommandType>

  14:    <odc:CommandText>select {[Measures].[Internet Sales Amount], 

  15:     [Measures].[Internet Tax Amount]} on  0, 

  16:     [Date].[Calendar Year].members on 1 from [Adventure Works]

  17:     </odc:CommandText>

  18:   </odc:Connection>

  19:  </odc:OfficeDataConnection>

  20: </xml>

   1: <xml id=docprops><o:DocumentProperties

   2:   xmlns:o="urn:schemas-microsoft-com:office:office"

   3:   xmlns="http://www.w3.org/TR/REC-html40">

   4:   <o:Name>SSAS Table Test</o:Name>

   5:  </o:DocumentProperties>

   6: </xml><xml id=msodc><odc:OfficeDataConnection

   7:   xmlns:odc="urn:schemas-microsoft-com:office:odc"

   8:   xmlns="http://www.w3.org/TR/REC-html40">

   9:   <odc:Connection odc:Type="OLEDB">

  10:    <odc:ConnectionString>Provider=MSOLAP.4;Integrated Security=SSPI;

  11:     Persist Security Info=True;Data Source=localhost;

  12:     Initial Catalog=Adventure Works DW 2008</odc:ConnectionString>

  13:    <odc:CommandType>Table</odc:CommandType>

  14:    <odc:CommandText>Adventure Works.$Source Currency</odc:CommandText>

  15:   </odc:Connection>

  16:  </odc:OfficeDataConnection>

  17: </xml>

Converting Multiple Rows into a CSV String (Set Based Method)

original code for Converting Multiple Rows into a CSV String (Set Based Method)
http://www.sqlteam.com/article/converting-multiple-rows-into-a-csv-string-set-based-method


create table #Page47 (
i int not null,
vc varchar(5) not null,
constraint pk_Page47 primary key (i,vc) )
go
set nocount on
declare @i int
select @i = 0
while @i <5000
begin
insert into #Page47 (i,vc)
select round((rand() * 100), 0),
char(round((rand() * 25), 0) + 97) +
char(round((rand() * 25), 0) + 97) +
char(round((rand() * 25), 0) + 97) +
char(round((rand() * 25), 0) + 97) +
char(round((rand() * 25), 0) + 97)
select @i = @i + 1
end
go
--create a table to work with
create table #workingtable (
i int not null,
vc varchar(5) not null,
list varchar(8000),
constraint pk_wt primary key (i,vc) )
insert into #workingtable (i,vc)
select i,vc
from #Page47
order by i,vc
declare
@list varchar(8000),
@lasti int
select
@list = '',
@lasti = -1
--here is the meat of the work
update
#workingtable
set
@list = list = case
when @lasti <> i then vc
else @list + ', ' + vc
end,
@lasti = i
--return a sample from the final rowset
select top 10
i,
case
when len(max(list)) > 50 then convert(varchar(50), left(max(list),47) + '...')
else convert(varchar(50),max(list))
end as list
from
#workingtable
group by
i
order by
newid()
go
i list
----------- --------------------------------------------------
127 itvgq, ljosw, nxmdj, oshrp, plxff, pubig, sthck...
849 gcifo, hbxkf, njkdl, sfesm, sjhky, uxhfq, vjeno...
684 fejly, fqyqf, gpfce, hutht, kwywo, mapco, momqn...
461 fsofv, fzked, murat, vzmek, yrqjo
612 nmmey, tfjhv, ulwuj, xxaaq
374 bbthd, jvjwz, klcsq, mrakf, peztf, pixww, rtwdd
730 dlynf, egqei, hhckx, nsvdn, obnhh, rfbwh, ytgfi
458 eijdr, gtxhu, lhtqh, phprf, qjhcr, vqnos
655 bijer, fwlgk, nrcbm, sohho, trjtw, usjdj, uvpie...
837 ayxcv, epurf, flvtj, ftxcj, imjap, pmygd, sqhcc...

Last executed sql by spid

DECLARE @handle VARBINARY(64)
SELECT @handle = sql_handle from sys.sysprocesses where spid = 55
SELECT text FROM sys.dm_exec_sql_text(@handle)

Shrink tempdb

USE [tempdb]
GO
DBCC SHRINKDATABASE(N'tempdb' )
GO

Stored procedure for truncating SQL 2008 Logs and shrinking the database

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create proc [dbo].[uspShrinkDatabase]
as
DECLARE @LogFileName sysname
DECLARE @DataFileName sysname
DECLARE @CatalogName sysname
set @CatalogName = db_name()
SELECT
@LogFileName = rtrim([name])
from
sys.database_files
where
type_desc = 'LOG'
SELECT
@DataFileName = rtrim([name])
from
sys.database_files
where
type_desc = 'rows'
select
@LogFileName as LogFileName,
@DataFileName as DataFileName
Checkpoint;
DBCC SHRINKFILE(@LogFileName, 1);
DBCC SHRINKDATABASE(@CatalogName);

Find slowest sproc

http://www.databasejournal.com/features/mssql/article.php/3802936/Finding-the-Worst-Performing-T-SQL-Statements-on-an-Instance.htm


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[usp_Worst_TSQL]
/*
Written by: Gregory A. Larsen
Copyright 2008 Gregory A. Larsen. All rights reserved.
Name: usp_Worst_TSQL
Description: This stored procedure displays the top worst performing queries based on CPU, Execution Count,
I/O and Elapsed_Time as identified using DMV information. This can be display the worst
performing queries from an instance, or database perspective. The number of records shown,
the database, and the sort order are identified by passing pararmeters.
Parameters: There are three different parameters that can be passed to this procedures: @DBNAME, @COUNT
and @ORDERBY. The @DBNAME is used to constraint the output to a specific database. If
when calling this SP this parameter is set to a specific database name then only statements
that are associated with that database will be displayed. If the @DBNAME parameter is not set
then this SP will return rows associated with any database. The @COUNT parameter allows you
to control the number of rows returned by this SP. If this parameter is used then only the
TOP x rows, where x is equal to @COUNT will be returned, based on the @ORDERBY parameter.
The @ORDERBY parameter identifies the sort order of the rows returned in descending order.
This @ORDERBY parameters supports the following type: CPU, AE, TE, EC or AIO, TIO, ALR, TLR, ALW, TLW, APR, and TPR
where "ACPU" represents Average CPU Usage
"TCPU" represents Total CPU usage
"AE" represents Average Elapsed Time
"TE" represents Total Elapsed Time
"EC" represents Execution Count
"AIO" represents Average IOs
"TIO" represents Total IOs
"ALR" represents Average Logical Reads
"TLR" represents Total Logical Reads
"ALW" represents Average Logical Writes
"TLW" represents Total Logical Writes
"APR" represents Average Physical Reads
"TPR" represents Total Physical Read
Typical execution calls
Top 6 statements in the AdventureWorks database base on Average CPU Usage:
EXEC usp_Worst_TSQL @DBNAME='AdventureWorks',@COUNT=6,@ORDERBY='ACPU';
Top 100 statements order by Average IO
EXEC usp_Worst_TSQL @COUNT=100,@ORDERBY='ALR';
Show top all statements by Average IO
EXEC usp_Worst_TSQL;
*/
(@DBNAME VARCHAR(128) = ''
,@COUNT INT = 999999999
,@ORDERBY VARCHAR(4) = 'AIO')
AS
-- Check for valid @ORDERBY parameter
IF ((SELECT CASE WHEN
@ORDERBY in ('ACPU','TCPU','AE','TE','EC','AIO','TIO','ALR','TLR','ALW','TLW','APR','TPR')
THEN 1 ELSE 0 END) = 0)
BEGIN
-- abort if invalid @ORDERBY parameter entered
RAISERROR('@ORDERBY parameter not APCU, TCPU, AE, TE, EC, AIO, TIO, ALR, TLR, ALW, TLW, APR or TPR',11,1)
RETURN
END
SELECT TOP (@COUNT)
COALESCE(DB_NAME(st.dbid),
DB_NAME(CAST(pa.value AS INT))+'*',
'Resource') AS [Database Name]
-- find the offset of the actual statement being executed
,SUBSTRING(text,
CASE WHEN statement_start_offset = 0
OR statement_start_offset IS NULL
THEN 1
ELSE statement_start_offset/2 + 1 END,
CASE WHEN statement_end_offset = 0
OR statement_end_offset = -1
OR statement_end_offset IS NULL
THEN LEN(text)
ELSE statement_end_offset/2 END -
CASE WHEN statement_start_offset = 0
OR statement_start_offset IS NULL
THEN 1
ELSE statement_start_offset/2 END + 1
) AS [Statement]
,OBJECT_SCHEMA_NAME(st.objectid,dbid) [Schema Name]
,OBJECT_NAME(st.objectid,dbid) [Object Name]
,objtype [Cached Plan objtype]
,execution_count [Execution Count]
,(total_logical_reads + total_logical_writes + total_physical_reads )/execution_count [Average IOs]
,total_logical_reads + total_logical_writes + total_physical_reads [Total IOs]
,total_logical_reads/execution_count [Avg Logical Reads]
,total_logical_reads [Total Logical Reads]
,total_logical_writes/execution_count [Avg Logical Writes]
,total_logical_writes [Total Logical Writes]
,total_physical_reads/execution_count [Avg Physical Reads]
,total_physical_reads [Total Physical Reads]
,total_worker_time / execution_count [Avg CPU]
,total_worker_time [Total CPU]
,total_elapsed_time / execution_count [Avg Elapsed Time]
,total_elapsed_time [Total Elasped Time]
,last_execution_time [Last Execution Time]
FROM sys.dm_exec_query_stats qs
JOIN sys.dm_exec_cached_plans cp ON qs.plan_handle = cp.plan_handle
CROSS APPLY sys.dm_exec_sql_text(qs.plan_handle) st
OUTER APPLY sys.dm_exec_plan_attributes(qs.plan_handle) pa
WHERE attribute = 'dbid' AND
CASE when @DBNAME = '' THEN ''
ELSE COALESCE(DB_NAME(st.dbid),
DB_NAME(CAST(pa.value AS INT)) + '*',
'Resource') END
IN (RTRIM(@DBNAME),RTRIM(@DBNAME) + '*')
ORDER BY CASE
WHEN @ORDERBY = 'ACPU' THEN total_worker_time / execution_count
WHEN @ORDERBY = 'TCPU' THEN total_worker_time
WHEN @ORDERBY = 'AE' THEN total_elapsed_time / execution_count
WHEN @ORDERBY = 'TE' THEN total_elapsed_time
WHEN @ORDERBY = 'EC' THEN execution_count
WHEN @ORDERBY = 'AIO' THEN (total_logical_reads + total_logical_writes + total_physical_reads) / execution_count
WHEN @ORDERBY = 'TIO' THEN total_logical_reads + total_logical_writes + total_physical_reads
WHEN @ORDERBY = 'ALR' THEN total_logical_reads / execution_count
WHEN @ORDERBY = 'TLR' THEN total_logical_reads
WHEN @ORDERBY = 'ALW' THEN total_logical_writes / execution_count
WHEN @ORDERBY = 'TLW' THEN total_logical_writes
WHEN @ORDERBY = 'APR' THEN total_physical_reads / execution_count
WHEN @ORDERBY = 'TPR' THEN total_physical_reads
END DESC

Remove carriage returns, tab, line feed from Excel text

=TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(J12, CHAR(10), " "), CHAR(13), " "), CHAR(9), " "), " ", " "))

getting user account for security in MS SQL

select PARSENAME(REPLACE(SUSER_NAME(), '\', '.'), 1)

select SUSER_NAME()

Remote Server Administration Tools for Vista

http://www.trainsignaltraining.com/windows-vista-rsat/2008-04-03/

Installing Remote Server Administration Tools for Vista
There are two versions of the RSAT tool, one for 32-bit machines and one for 64-bit machines. You can download the version you need at their respective download locations:
Download: Remote Server Administration Tools (x86)
Download: Remote Server Administration Tools (x64)

http://www.microsoft.com/downloads/details.aspx?FamilyId=9FF6E897-23CE-4A36-B7FC-D52065DE9960&displaylang=en
http://www.microsoft.com/downloads/details.aspx?FamilyId=D647A60B-63FD-4AC5-9243-BD3C497D2BC5&displaylang=en

SQL Round to day

DATEADD(Day, DATEDIFF(Day, 0, DateValue), 0)

BGINFO - display confirgutaion properties on desktop

 http://technet.microsoft.com/en-us/sysinternals/bb897557.aspx

Vista and Windows Server 2008 the "All Users" folder

Vista and Windows Server 2008 the "All Users" folder is at the following path

C:\ProgramData\Microsoft\Windows\Start Menu\Programs\Startup

List all the columns in the all the tables in all the databases

List all the columns in all the databases.


/*

CREATE TABLE #TmpColumns(
[TABLE_CATALOG] [nvarchar](128) NULL,
[TABLE_SCHEMA] [nvarchar](128) NULL,
[TABLE_NAME] [sysname] NOT NULL,
[COLUMN_NAME] [sysname] NULL,
[ORDINAL_POSITION] [int] NULL,
[COLUMN_DEFAULT] [nvarchar](4000) NULL,
[IS_NULLABLE] [varchar](3) NULL,
[DATA_TYPE] [nvarchar](128) NULL,
[CHARACTER_MAXIMUM_LENGTH] [int] NULL,
[CHARACTER_OCTET_LENGTH] [int] NULL,
[NUMERIC_PRECISION] [tinyint] NULL,
[NUMERIC_PRECISION_RADIX] [smallint] NULL,
[NUMERIC_SCALE] [int] NULL,
[DATETIME_PRECISION] [smallint] NULL,
[CHARACTER_SET_CATALOG] [sysname] NULL,
[CHARACTER_SET_SCHEMA] [sysname] NULL,
[CHARACTER_SET_NAME] [sysname] NULL,
[COLLATION_CATALOG] [sysname] NULL,
[COLLATION_SCHEMA] [sysname] NULL,
[COLLATION_NAME] [sysname] NULL,
[DOMAIN_CATALOG] [sysname] NULL,
[DOMAIN_SCHEMA] [sysname] NULL,
[DOMAIN_NAME] [sysname] NULL
) ON [PRIMARY]

insert into #TmpColumns
exec dbo.uspSearchAllColumnNames
@Database = null,
@Schema = null,
@Table = null,
@Column = '%office%'

select
*
from
#TmpColumns
*/

CREATE PROC [dbo].[uspSearchAllColumnNames](
@Database sysname = null,
@Schema sysname = null,
@Table sysname = null,
@Column sysname = null
)
as

DECLARE @Template NVARCHAR(2000)
DECLARE @Sql NVARCHAR(2000)
DECLARE @Catalog sysname
DECLARE @RowId int
DECLARE @MaxCnt int
SELECT
*
INTO #Columns
FROM
INFORMATION_SCHEMA.COLUMNS
WHERE
1=0
SET @Template = '
INSERT INTO #Columns
SELECT
*
FROM
[<>].INFORMATION_SCHEMA.COLUMNS
'
SELECT
IDENTITY(INT,1,1) AS RowID,
[Name]
INTO #DBNames
FROM
sys.databases
WHERE
owner_sid <> 0x01
SELECT
@RowId = 1,
@MaxCnt = COUNT(*)
FROM
#DBNames
WHILE (@RowId <= @MaxCnt)
BEGIN
SELECT
@Catalog = [Name]
FROM
#DBNames
WHERE
@RowId = RowId
SET @Sql = REPLACE(@Template, '<>', @Catalog)
EXEC sp_executesql @Sql
SET @RowId = @RowId +1
end


/*
--debugging
declare
@Database sysname,
@Schema sysname,
@Table sysname,
@Column sysname
SET @Column = '%vista%'
*/
SELECT
*
FROM
#Columns
where
(@Database IS NULL OR TABLE_CATALOG LIKE @Database)
AND (@Schema IS NULL OR TABLE_SCHEMA LIKE @Schema)
AND (@Table IS NULL OR TABLE_NAME LIKE @Table)
AND (@Column IS NULL OR COLUMN_NAME LIKE @Column)
ORDER BY
TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, ORDINAL_POSITION, COLUMN_DEFAULT, IS_NULLABLE, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, CHARACTER_OCTET_LENGTH, NUMERIC_PRECISION, NUMERIC_PRECISION_RADIX, NUMERIC_SCALE, DATETIME_PRECISION, CHARACTER_SET_CATALOG, CHARACTER_SET_SCHEMA, CHARACTER_SET_NAME, COLLATION_CATALOG, COLLATION_SCHEMA, COLLATION_NAME, DOMAIN_CATALOG, DOMAIN_SCHEMA, DOMAIN_NAME


DROP TABLE #Columns
DROP TABLE #DBNames

Finding the largest table in a database

A good article with SQL script for calculating the largest SQL tables inside a database including the size occupied by the indexes:
http://www.sqlteam.com/article/finding-the-biggest-tables-in-a-database



/**************************************************************************************
*
* BigTables.sql
* Bill Graziano (SQLTeam.com)
* graz@sqlteam.com
* v1.1
*
**************************************************************************************/
declare @id int
declare @type character(2)
declare @pages int
declare @dbname sysname
declare @dbsize dec(15,0)
declare @bytesperpage dec(15,0)
declare @pagesperMB dec(15,0)
create table #spt_space
(
objid int null,
rows int null,
reserved dec(15) null,
data dec(15) null,
indexp dec(15) null,
unused dec(15) null
)
set nocount on
-- Create a cursor to loop through the user tables
declare c_tables cursor for
select id
from sysobjects
where xtype = 'U'
open c_tables
fetch next from c_tables
into @id
while @@fetch_status = 0
begin
/* Code from sp_spaceused */
insert into #spt_space (objid, reserved)
select objid = @id, sum(reserved)
from sysindexes
where indid in (0, 1, 255)
and id = @id
select @pages = sum(dpages)
from sysindexes
where indid < 2
and id = @id
select @pages = @pages + isnull(sum(used), 0)
from sysindexes
where indid = 255
and id = @id
update #spt_space
set data = @pages
where objid = @id
/* index: sum(used) where indid in (0, 1, 255) - data */
update #spt_space
set indexp = (select sum(used)
from sysindexes
where indid in (0, 1, 255)
and id = @id)
- data
where objid = @id
/* unused: sum(reserved) - sum(used) where indid in (0, 1, 255) */
update #spt_space
set unused = reserved
- (select sum(used)
from sysindexes
where indid in (0, 1, 255)
and id = @id)
where objid = @id
update #spt_space
set rows = i.rows
from sysindexes i
where i.indid < 2
and i.id = @id
and objid = @id
fetch next from c_tables
into @id
end
select top 25
Table_Name = (select left(name,25) from sysobjects where id = objid),
rows = convert(char(11), rows),
reserved_KB = ltrim(str(reserved * d.low / 1024.,15,0) + ' ' + 'KB'),
data_KB = ltrim(str(data * d.low / 1024.,15,0) + ' ' + 'KB'),
index_size_KB = ltrim(str(indexp * d.low / 1024.,15,0) + ' ' + 'KB'),
unused_KB = ltrim(str(unused * d.low / 1024.,15,0) + ' ' + 'KB')
from #spt_space, master.dbo.spt_values d
where d.number = 1
and d.type = 'E'
order by reserved desc
drop table #spt_space
close c_tables
deallocate c_tables