Jira Automation-Regel beim Mergen von Pull-Anfragen
Posted by: AJ Welch
While extremely powerful as a relational database, SQL Server can be somewhat daunting at times when it comes to looking up underlying information about the database system itself.
To relieve these headaches in some small part, we’ll briefly explore how to find all tables in the database which contain a particular column name.
Mehr als nur Agile
One basic concept to understand about SQL Server is that of catalog views, which are effectively database tables (catalogs in this case) that display system-wide information about the SQL Server Database Engine.
Mehr als nur Agile
All catalog views are accessed via a SELECT SQL statement FROM a specific catalog within the sys. namespace.
For example, the following statement can be used to view information about all database tables in the system via the sys.tables catalog:
SELECT
*
FROM
sys.tablesMehr als nur Agile
Before we get into how to extract all tables with a particular name, we should briefly explore what the LIKE statement does, as well as the wildcard (%) symbol, and how they are used together.
LIKE is used in a query to determine if a particular pattern of characters (typically the values of a specified column) match a formatted string of characters.
LIKE is often also used in conjunction with the % character, which represents a wildcard when attempting to match the pattern. When a % wildcard character is present in the pattern string, it indicates that any characters can be present in that location of the pattern string and still be considered a match.
For example, if we want to find all books where the title begins with “The” but can contain any characters thereafter, we’d use a statement like so:
SELECT
title,
primary_author,
published_date
FROM
books
WHERE
title LIKE 'The%'Observant readers might realize that the above pattern would not only match titles that had “The” at their beginning, but also any titles with words simply starting with the three letters “The” as well. Since % wildcards match any characters, if we only want to check for titles with the word “The”, adding a space is more appropriate:
SELECT
title,
primary_author,
published_date
FROM
books
WHERE
title LIKE 'The %'Mehr als nur Agile
With our basic knowledge of both catalog views and the LIKE statement, we are now equipped to lookup all the tables in our system that contain a particular column name:
SELECT
sys.columns.name AS ColumnName,
tables.name AS TableName
FROM
sys.columns
JOIN sys.tables ON
sys.columns.object_id = tables.object_id
WHERE
sys.columns.name = 'ColumnName'
We need to combine information from two catalogs, sys.tables and sys.columns, so we’re using a JOIN statement. The two are associated by the object_id field, so we JOIN on that field.
From there, it’s a simple matter of selecting the ColumnName and TableName of our results, and finally, of course, only looking up records where sys.columns.name is equal to our ColumnName string.
However, this query will only find exact matches of the column name. If we want to find partial matches, we can use LIKE and % wildcard characters instead:
SELECT
sys.columns.name AS ColumnName,
tables.name AS TableName
FROM
sys.columns
JOIN sys.tables ON
sys.columns.object_id = tables.object_id
WHERE
sys.columns.name LIKE '%ColumnName%'There we have it! A simple query to look up all tables and associated columns with a particular (or similar) column name in them.