SQL Basics - Finding tables
We are in part two of our four part series around basic operations in TSQL. Today we will be finding tables based on certain criteria. Here are a couple I will show:
- Finding table names matching a string
- Finding tables containing a certain data type
- Excluding system objects from queries for tables
The first item is pretty straightforward and mirrors our prior discussion on columns. I will show you four ways to query for a list of tables matching a string.
Option 1 uses the sys.tables catalog view. This view returns a row for each table object, currently user tables only. The syntax is:
SELECT *
FROM sys.tables
WHERE name LIKE 'Product%';
Option 2 uses the sys.objects catalog view. This view returns a row for each user defined object in a database. The syntax is:
SELECT *
FROM sys.objects
WHERE type = 'U'
AND name LIKE 'Product%';
Option 3 uses the sys.sysobjects compatibility view. This view contains one row for each object in a database. This view is created for backward compatibility, so its future is not guaranteed. The syntax is:
SELECT *
FROM sys.sysobjects
WHERE type = 'U'
AND name LIKE 'Product%';
Option 4 uses the INFORMATION_SCHEMA.TABLES view. This view returns one row for each table in the current database that can be accessed by the current user in the current database. The syntax is:
SELECT *
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME LIKE 'Product%';
To find tables containing certain data types we have two options. The sys.tables, sys.objects, or sys.sysobjects views could be used in the manner below (swapping out [object_id] for [id] if using sys.sysobjects).
SELECT t.name AS tableName
,c.name AS columnName
,ty.name AS dataType
FROM sys.tables AS t
INNER JOIN sys.columns AS c ON c.[object_id] = t.[object_id]
INNER JOIN sys.types AS ty ON ty.system_type_id = c.system_type_id
WHERE ty.name IN('varchar', 'nvarchar');
The same thought could be expressed a little differently against INFORMATION_SCHEMA. The syntax is:
SELECT *
FROM INFORMATION_SCHEMA.TABLES AS T
INNER JOIN INFORMATION_SCHEMA.COLUMNS AS C ON C.TABLE_NAME = T.TABLE_NAME
WHERE C.DATA_TYPE IN('varchar', 'nvarchar');
For certain searches for tables you might want to exclude the system objects stored in meta data. One method for this I have already mentioned, indirectly, is the sys.tables compatibility view. For now it only contains user defined objects. It does, however, contain a very important column named is_ms_shipped. This column also exists in sys.objects and allows us to effectively filter from those tables. Your syntax would simply be:
SELECT *
FROM sys.tables
WHERE is_ms_shipped = 0;
or
SELECT *
FROM sys.objects
WHERE is_ms_shipped = 0;
An additional option is to make use of the OBJECTPROPERTY function. This would allow us to use sys.sysobjects as well. The syntax would be:
SELECT *
FROM sys.sysobjects
WHERE type = 'U'
AND OBJECTPROPERTY(id, 'IsMSShipped') = 0;
Up next, identity columns. I hope this is helpful for everyone, if not at least it will be over quickly. :)