在SQL数据库中搜索对象的不同方法

This article explores various ways to search for database objects in SQL database such as tables, stored procedures, functions, and views.

本文探讨了在SQL数据库中搜索数据库对象（例如表，存储过程，函数和视图）的各种方法。

介绍 (Introduction)

SQL Server has many database objects such as table, view, stored procedure, function, constraints, rule, Synonym, triggers. You being a developer or database administrator might work with thousands of databases. Although you should use a proper naming convention for database objects, it is a difficult task to remember the object names. Let’s consider a single production instance that consists of many databases. You want to look for a specific database object but not sure it exists in which database. It is not possible to explore each database and view the object.

SQL Server具有许多数据库对象，例如表，视图，存储过程，函数，约束，规则，同义词，触发器。 您作为开发人员或数据库管理员可能会处理数千个数据库。 尽管您应该为数据库对象使用正确的命名约定，但是记住对象名称是一项艰巨的任务。 让我们考虑一个由许多数据库组成的生产实例。 您要查找特定的数据库对象，但不确定该对象在哪个数据库中。 无法浏览每个数据库并查看对象。

In this article, we explore various ways to search for database objects in the SQL database.

在本文中，我们探索了在SQL数据库中搜索数据库对象的各种方法。

使用sys.objects系统目录视图 (Use sys.objects system catalog view)

We can use system catalog view sys.objects to view all objects in a SQL database. It has a column type that contains the object category. For example, if we want to search only for the user-defined table, we use ‘U’ value for the type column.

我们可以使用系统目录视图sys.objects来查看SQL数据库中的所有对象。 它具有包含对象类别的列类型。 例如，如果我们只想搜索用户定义的表，则对类型列使用“ U”值。

I filter records for the useful columns. You can get all columns using the select * statement.

我过滤了有用列的记录。 您可以使用select *语句获取所有列。

USE [AdventureWorks];
GO
SELECT name AS [Name], 
       SCHEMA_NAME(schema_id) AS schema_name, 
       type_desc, 
       create_date, 
       modify_date
FROM sys.objects
WHERE type ='u'

Similarly, we use the value ‘P’ for the stored procedure.

同样，对于存储过程，我们使用值“ P” 。

USE [AdventureWorks];
GO
SELECT name AS [Name], 
       SCHEMA_NAME(schema_id) AS schema_name, 
       type_desc, 
       create_date, 
       modify_date
FROM sys.objects
WHERE type ='p'

You can also search for objects modified in ‘N’ number of days. In the below query, we try to find output objects changed in the last 60 days in the sample database [adventureWorks].

您还可以搜索在'N'天后修改的对象。 在下面的查询中，我们尝试在示例数据库[adventureWorks]中查找最近60天内更改的输出对象。

USE [AdventureWorks];
GO
SELECT name AS [Name], 
       SCHEMA_NAME(schema_id) AS schema_name, 
       type_desc, 
       create_date, 
       modify_date
FROM sys.objects
WHERE modify_date > GETDATE() - 60
ORDER BY modify_date;  
GO

You can refer to Microsoft docs for different values for the Type column in the sys.objects.

您可以针对sys.objects中“类型”列的不同值参考Microsoft文档 。

使用系统信息架构视图 (Use System Information Schema views)

We can also use information schema views to search for the specific SQL database objects. For example, we use information_schema.Tables to find out specific tables in the SQL database.

我们还可以使用信息架构视图来搜索特定SQL数据库对象。 例如，我们使用information_schema.Tables查找SQL数据库中的特定表。

In the script below, we search for the [Demotable].

在下面的脚本中，我们搜索[Demotable]。

SELECT *
FROM information_schema.Tables
WHERE [Table_Name]='demotable'