Get-TraceFlags.ps1

<#
	.SYNOPSIS
		Designed to retrieve global trace flags that are set on a server.
	
	.DESCRIPTION
		Designed to retrieve global trace flags that are set on a server. It will also return whether those trace flags are startup flags, or enabled post startup.
	
	.PARAMETER SqlServer
		Required: The SQL Server instance to query
	
	.EXAMPLE
		Get-TraceFlags -sqlserver LOCALHOST

		Returns enabled trace flags for the given instance


#>


function Get-TraceFlags
{
	[CmdletBinding()]
	param
	(
	[Parameter(Mandatory = $true,
			   Position = 1)]
	[string]
	$SqlServer
	)
	
	
	$TraceFlagQuery = @"
SET NOCOUNT ON;

IF (
     SELECT OBJECT_ID(N'tempdb..#TraceStatus')
   ) IS NULL
    CREATE TABLE #TraceStatus
        (
          TraceFlag VARCHAR(10) COLLATE SQL_Latin1_General_CP1_CI_AS ,
          [Status] CHAR(1) ,
          [Global] CHAR(1) ,
          [Session] CHAR(1)
        );

TRUNCATE TABLE #TraceStatus;

INSERT  INTO #TraceStatus
        EXEC sp_executesql N'DBCC TRACESTATUS WITH NO_INFOMSGS;';
WITH    cteStartupFlags
          AS (
               SELECT   SUBSTRING(CAST(value_data AS VARCHAR(10)), 3, LEN(CAST(value_data AS VARCHAR(10))) - 3) AS StartupFlag
               FROM     sys.dm_server_registry
               WHERE    value_name LIKE 'SQLArg%'
                        AND CAST(value_data AS VARCHAR(10)) LIKE '-T%'
             )
    SELECT  t.TraceFlag ,
            t.[Status] ,
            t.[Global] ,
            t.[Session] ,
            CASE WHEN StartupFlag IS NOT NULL THEN 1
                 ELSE 0
            END AS IsStartupFlag
    FROM    #TraceStatus t
            LEFT JOIN cteStartupFlags c ON t.TraceFlag = c.StartupFlag;


"@
	
	Write-Verbose $TraceFlagQuery;
	
	$TraceFlagResults = invoke-sql -datasource $SqlServer -database master -SqlCommand $TraceFlagQuery;
	$TraceFlagResults | Sort-Object TraceFlag;
}