Generate T-SQL Script for a List of Views

USE:

CLEAR 

$sSRCServer = "TUIRA"
$sSRCDatabase = "AdventureWorks"

$oSO = New-SqlScriptingOptions
$oSO.Permissions = $true
$oSO.IncludeIfNotExists = $true

$a = Get-Content "C:tempviewlist.txt"

foreach ($o in $a) {
	Get-SqlDatabase $sSRCServer $sSRCDatabase|Get-SqlView -Name $o| `
                     Get-SqlScripter -scriptingOptions $oSO
	WRITE("GO `n`n`n")
}

WMI Date Conversion

USE: To convert the GMT data returned from error logs to a readable date time value.

CLEAR

$sHostName = "TUIRA"
$aLogName = "Application"
$FromDate = [datetime]::today
$WMIQuery = "LogFile='$aLogName' and Type='Error' and TimeGenerated>='$FromDate'"
 
Get-WmiObject Win32_NTLogEvent -ComputerName $sHostName `
                               -Filter "$WMIQuery" `
							   -ErrorAction SilentlyContinue | `
							    SELECT SourceName, `
 @{Name = "TimeGenerated"; EXPRESSION = {$_.ConvertToDateTime($_.TimeGenerated)}}, `
							    Message

Top 25 Most Expensive Stored Procedures

USE: These queries will display the top 25 most expensive stored procedures that are still in the cache.

SET NOCOUNT ON

--[ REVIEW MOST EXPENSIVE TOP 25 CACHED STORED PROCEDURES ]
--[ LOGICAL READS RELATE TO MEMORY PRESSURE               ]
SELECT TOP(25) A.name [SP Name]
     , B.total_logical_reads [TotalLogicalReads]
     , B.total_logical_reads / B.execution_count [AvgerageLogicalReads]
     , B.execution_count
     , ISNULL(B.execution_count / DATEDIFF(SECOND, B.cached_time, GETDATE()), 0) [Calls/Second]
     , B.total_elapsed_time
     , B.total_elapsed_time / B.execution_count [AverageElapsedTime]
     , B.cached_time
  FROM sys.procedures A
 INNER JOIN sys.dm_exec_procedure_stats B ON A.[object_id] = B.[object_id]
 WHERE B.database_id = DB_ID()
 ORDER BY B.total_logical_reads DESC;
 
 
 
--[ REVIEW MOST EXPENSIVE TOP 25 CACHED STORED PROCEDURES ]
--[ PHYSICAL READS RELATE TO IO PRESSURE                  ]
SELECT TOP(25) A.name [SP Name]
     , B.total_physical_reads [TotalphysicalReads]
     , B.total_physical_reads / B.execution_count [AvgeragePhysicalReads]
     , B.execution_count
     , ISNULL(B.execution_count / DATEDIFF(SECOND, B.cached_time, GETDATE()), 0) [Calls/Second]
     , B.total_elapsed_time
     , B.total_elapsed_time / B.execution_count [AvgerageElapsedTime]
     , B.cached_time
  FROM sys.procedures A
 INNER JOIN sys.dm_exec_procedure_stats B ON A.[object_id] = B.[object_id]
 WHERE B.database_id = DB_ID()
 ORDER BY B.total_physical_reads DESC;

PowerShell and Outlook

This script was written by ed wilson at Use PowerShell to Data Mine Your Outlook Inbox

CLEAR

Function Get-OutlookInBox {
 Add-type -assembly "Microsoft.Office.Interop.Outlook" | out-null
 $olFolders = "Microsoft.Office.Interop.Outlook.olDefaultFolders" -as [type]
 
 $outlook = new-object -comobject outlook.application
 $namespace = $outlook.GetNameSpace("MAPI")
 $folder = ($namespace.getDefaultFolder($olFolders::olFolderInBox)).folders|WHERE {$_.Name -eq "DDUMP"}
 $folder.items | Select-Object -Property Subject, ReceivedTime, Importance, SenderName
} #end function Get-OutlookInbox

Get-OutlookInBox |where { $_.ReceivedTime -gt [datetime]"6/25/12"} | sort importance

Script Out All Indexes and Primary Keys from a Database

USE: To generate T-SQL code for all indexes and PK constraints from a database.

CLEAR 

$sSRCServer = "TPW520"
$sSRCDatabase = "iSQLPS" 

$oSO = New-SqlScriptingOptions
$oSO.ClusteredIndexes = $true
$oSO.Indexes = $true
$oSO.NonClusteredIndexes = $true
$oSO.IncludeIfNotExists = $true

$oTables = Get-SqlDatabase $sSRCServer $sSRCDatabase|Get-SqlTable

foreach ($oTable in $oTables){
    $oT = $oTable.Indexes
    
	foreach ($oIndex in $oT) {
		$DDL = Get-SqlScripter $oIndex -scriptingOptions $oSO
	    
		"$DDL `nGO `n`n"
	}
}

Create Time String

USE: This function will return a string representation of time.

CREATE FUNCTION [dbo].[fnCreateTimeString] (
       @seconds int)

RETURNS varchar(75) AS 

BEGIN
    DECLARE @h int,
            @m int,
            @s int,
            @secs int,
            @BuildDate varchar(75),
            @hour varchar(75),
            @minute varchar(50),
            @second varchar(25)

    SELECT @secs = @seconds
    SELECT @h = @secs / 3600
    SELECT @secs = @secs - (@h * 3600)
    SELECT @m = @secs / 60
    SELECT @secs = @secs - (@m * 60)
    SELECT @s = @secs

    SELECT @second = CASE WHEN @s = 1 THEN CAST(@s AS varchar) +' second'
                                      ELSE CAST(@s AS varchar) +' seconds' END

    SELECT @minute = CASE WHEN @m = 1 THEN CAST(@m AS varchar) +' minute with '+ @second
                                      ELSE CAST(@m AS varchar) +' minutes with '+ @second END

    SELECT @hour = CASE WHEN @h = 1 THEN CAST(@h AS varchar) +' hour '+ @minute + @second
                                    ELSE CAST(@h AS varchar) +' hours '+ @minute + @second END

    IF @h = 0
       BEGIN
       IF @m = 0
          BEGIN
          SELECT @BuildDate = @second
       END
       ELSE
          BEGIN
          SELECT @BuildDate = @minute
       END
    END
    ELSE
       BEGIN
       SELECT @BuildDate = @hour
    END

    RETURN CONVERT(varchar(75), @BuildDate)
END
GO

Index Information

USE: To display index information.

SET NOCOUNT ON

SELECT DB_NAME(IPS.database_id) AS [database_name]
     , OBJECT_NAME(IPS.object_id, IPS.database_id) AS [object_name]
     , QUOTENAME(I.name) [index_name]
     , CASE WHEN avg_fragmentation_in_percent &lt; 10 THEN &#039;LOW&#039;
            WHEN avg_fragmentation_in_percent &lt; 30 THEN &#039;MEDIUM&#039;
            WHEN avg_fragmentation_in_percent &lt; 50 THEN &#039;HIGH&#039;
            ELSE &#039;EXTREME&#039; END [fragmentation_indicator]
     , IPS.index_id
     , IPS.index_type_desc
     , IPS.avg_fragmentation_in_percent
     , IPS.fragment_count
     , IPS.avg_fragment_size_in_pages
     , IPS.page_count
  FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL , &#039;LIMITED&#039;) AS IPS
 INNER JOIN sys.indexes I  ON IPS.object_id = I.object_id 
   AND IPS.index_id = I.index_id
 ORDER BY [object_name], [index_name]
GO

Table Constraints

USE: To display all table constraints from a database.

SET NOCOUNT ON

SELECT A.name [TableName]
     , B.name [ColumnName]
     , C.name [ConstraintName]
     , C.[type] [ConstraintType]
     , A.crdate
  FROM sysobjects A
 INNER JOIN sys.columns B ON A.id = B.object_id
 INNER JOIN sys.key_constraints C ON A.id = C.parent_object_id
 WHERE A.Xtype = 'U'
   AND B.is_identity = 1
 ORDER BY 1
GO

Database Information

USE: To display database detail information.

SELECT @@SERVERNAME [ServerName],
       MF.data_space_id,
       MF.database_id,
       MF.[type],
       MF.[type_desc],
       MF.[name] [DBName],
       MF.[physical_name] [FilePath],
       MF.[state_desc],
       CAST((MF.[size] * 8.0) / 1024.0 AS numeric(19,2)) [FileSize (MB)],
       MF.[max_size],
       MF.[growth],
       DB.[create_date],
       DB.[compatibility_level],
       DB.[collation_name],
       DB.[user_access_desc],
       DB.[state_desc],
       DB.[recovery_model]
  FROM sys.master_files MF
 INNER JOIN sys.databases DB ON MF.database_id = DB.database_id
  LEFT JOIN sys.data_spaces DS ON MF.data_space_id = DS.data_space_id
  LEFT JOIN sys.database_files DF ON DS.name = DF.name
GO

Database Size

USE: To display database file size information at the server level.

SELECT @@SERVERNAME [ServerName],
       MF.database_id,
       DB.[name] [DBName],
       SUM(CAST((MF.[size] * 8.0) / 1024.0 AS numeric(19,2))) [Total Space Usage (MB)]
  FROM sys.master_files MF
 INNER JOIN sys.databases DB ON MF.database_id = DB.database_id
 GROUP BY MF.database_id, DB.[name]
 ORDER BY MF.database_id
 GO