プル リクエストがマージされたときの Jira Automation ルール
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.
アジャイルを超える
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.
アジャイルを超える
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.tables
アジャイルを超える
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 %'
アジャイルを超える
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.