Month: June 2005

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 […]

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 = […]

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) […]

USE: To display index information.  

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 […]

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 […]

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  

USE: To capture the result from executing sp_who2 system stored procedure. SET NOCOUNT ON IF OBJECT_ID(‘tempdb..#SP_WHO2’) IS NOT NULL DROP TABLE #SP_WHO2 GO CREATE TABLE #SP_WHO2 ( SPID varchar(128) , [Status] char(12) , [Login] varchar(128) , [HostName] varchar(128) , [BlkBy] varchar(128) , [DBName] varchar(128) , [Command] varchar(128) , [CPUTime] varchar(128) , [DiskIO] varchar(128) , LastBatch […]

USE: To find out recent database files growth. IF(SELECT CONVERT(int,value_in_use) FROM sys.configurations WHERE [name] = ‘default trace enabled’ ) = 1 BEGIN declare @curr_tracefilename varchar(500) ; declare @base_tracefilename varchar(500) ; declare @indx int ; SELECT @curr_tracefilename = [path] FROM sys.traces where is_default = 1 ; SET @curr_tracefilename = REVERSE(@curr_tracefilename); SELECT @indx = PATINDEX(‘%%’, @curr_tracefilename) ; […]

DECLARE @TracePath nvarchar(256) SELECT @TracePath = [path] FROM master.sys.traces SELECT * FROM ::fn_trace_gettable (@TracePath,default) SELECT T.EventID , T.ColumnID , E.Name [EventDescription] , C.Name [ColumnDescription] FROM ::fn_trace_geteventinfo(1) T INNER JOIN sys.trace_events E ON T.EventID = E.trace_event_id INNER JOIN sys.trace_columns C ON T.columnid = C.trace_column_id SELECT T.EventID , E.Name [EventDescription] , COUNT(*) [DataColumnCount] FROM ::fn_trace_geteventinfo(1) T INNER […]