Skip to content

Instantly share code, notes, and snippets.

@CodyMathis123
Created May 18, 2022 17:48
Show Gist options
  • Save CodyMathis123/6ab4d0f0a0b29677be4f38d27eec16a1 to your computer and use it in GitHub Desktop.
Save CodyMathis123/6ab4d0f0a0b29677be4f38d27eec16a1 to your computer and use it in GitHub Desktop.
--Originally Written by: Narayana Vyas Kondreddi
--Modified By: Jon Mattivi
--Purpose: Search all tables and columns in the Orchestrator database to find variable instances
DECLARE @VarName nvarchar(100), @VarID nvarchar(100)
SET @VarName = 'MyVariableName'
SET @VarID = (Select VARIABLES.UniqueID
From VARIABLES
INNER JOIN OBJECTS ON OBJECTS.UniqueID = VARIABLES.UniqueID
Where OBJECTS.Name = @VarName and OBJECTS.Deleted != 1)
CREATE TABLE #Results (RunbookPath nvarchar(1000), RunbookName nvarchar(250), ActivityName nvarchar(250), [Table.Column] nvarchar(370))
SET NOCOUNT ON
DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110)
SET @TableName = ''
SET @SearchStr2 = QUOTENAME('%' + @VarID + '%','''')
WHILE @TableName IS NOT NULL
BEGIN
SET @ColumnName = ''
SET @TableName =
(
SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
AND QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
AND (TABLE_SCHEMA) = 'dbo'
AND OBJECTPROPERTY(
OBJECT_ID(
QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
), 'IsMSShipped'
) = 0
)
WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL) AND (@TableName != '[dbo].[OBJECTINSTANCEDATA]') AND (@TableName != '[dbo].[OBJECTINSTANCES]') AND (@TableName != '[dbo].[POLICYINSTANCES]') AND ((SELECT TOP 1 COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = PARSENAME(@TableName, 1) AND (COLUMN_NAME) = 'UniqueID' AND (DATA_TYPE) = 'uniqueidentifier' AND (TABLE_SCHEMA) = 'dbo') is not null)
BEGIN
SET @ColumnName =
(
SELECT MIN(QUOTENAME(COLUMN_NAME))
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = PARSENAME(@TableName, 2)
AND TABLE_NAME = PARSENAME(@TableName, 1)
AND DATA_TYPE IN ('char', 'datetime', 'decimal', 'int', 'money', 'ntext', 'nvarchar', 'varbinary', 'varchar')
AND QUOTENAME(COLUMN_NAME) > @ColumnName
)
IF @ColumnName IS NOT NULL
BEGIN
INSERT INTO #Results
EXEC
(
'IF EXISTS (Select TOP 1 ' + @TableName + '.' + @ColumnName + 'From ' + @TableName + ' (NOLOCK) WHERE ' + @TableName + '.' + @ColumnName + ' LIKE ' + @SearchStr2 + ')' +
'BEGIN ' +
'SELECT Resources2.[Path], Policy.[Name], ActObj.[Name],''' + @TableName + '.' + @ColumnName + '''' +
'FROM ' + @TableName + ' (NOLOCK) ' +
'INNER JOIN [dbo].[OBJECTS] ActObj (NOLOCK) ON ' + @TableName + '.[UniqueID] = ActObj.[UniqueID]' +
'INNER JOIN [dbo].[POLICIES] Policy (NOLOCK) ON ActObj.[ParentID] = Policy.[UniqueID]' +
'INNER JOIN [Microsoft.SystemCenter.Orchestrator.Internal].[Resources] Resources2 (NOLOCK) ON Policy.[UniqueID] = Resources2.[UniqueId]' +
'WHERE ' + @TableName + '.' + @ColumnName + ' LIKE ' + @SearchStr2 + 'and ActObj.[Deleted] != 1 ' +
'END'
)
END
END
END
SELECT * FROM #Results
Order By RunbookPath
DROP TABLE #Results
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment