Searching Your SQL Database For A Specified Column Name
Sometimes you may find it necessary to search for a specific SQL table, view or procedure column name to develop your SQL or even your WQL queries. Using the following SQL query you can locate a predefined column or header name by specifying and setting a Column Variable (ColVar).
In the SQL query below we are setting the Column Variable to %Time%. By placing the % symbol both before and after the word “Time” we are asking the SQL query analyzer to parse or search all of the internal system columns (SysColumns ) for the database from which the query is initiated from where the columns name contains the “Time” string.
The word “Time” will be enumerated with additional characters both before and after the word “Time” where the percent symbols (%) are set to function as wildcard placeholders as in the query output example below:
Collections - LastChangeTime
pOperating_System_DATA - @LastBootUpTime0
v_AgentDiscoveries - AgentTime
If you were to remove the percent symbols from around the word “Time” by setting the ColVar to simply “Time” it will return to you only those columns that contained the word “Time” as in the examples below:
StatusMessages - Time
v_StatusMessage - Time
vStatusMessages - Time
Additionally we can set only one percent symbol, either before or after the “Time” string to look for columns that have the header xxxTime or Timexxx contained within them. Changing the line to “Set @ColVar = 'Time%'” would return for example the ‘Sites’ table name with the ‘TimeZoneInfo’ column name. By changing the line to Set @ColVar = '%Time' it would return for example the ‘InventoryAction’ table with the ‘LastUpdateTime’ column name.
To run or execute the SQL query simply copy and paste the SQL query below into the SQL query analyzer (Isqlw.Exe from the start > run line) use the database dropdown arrow to select your SMS database change the Set @ColVar = to the keyword that you wish to locate and press the F5 keyboard shortcut to start the query parser.
SQL Query:
Set NoCount On
Declare @ColVar VarChar(30)
Set @ColVar = '%Time%' -- String To Find
Select
Convert(Char(75), SysObjects.Name) 'Table Names:',
Convert(Char(75), SysColumns.Name) 'Column Names:'
From SysObjects, SysColumns, SysTypes
Where SysObjects.ID = SysColumns.ID
And SysColumns.xType = SysTypes.xType
And SysColumns.Name like @ColVar
Order by SysObjects.Name Asc
Set NoCount Off
No Comments