Year: 2005

USE:  

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

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

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