DZone
Thanks for visiting DZone today,
Edit Profile
  • Manage Email Subscriptions
  • How to Post to DZone
  • Article Submission Guidelines
Sign Out View Profile
  • Post an Article
  • Manage My Drafts
Over 2 million developers have joined DZone.
Log In / Join
Please enter at least three characters to search
Refcards Trend Reports
Events Video Library
Refcards
Trend Reports

Events

View Events Video Library

Zones

Culture and Methodologies Agile Career Development Methodologies Team Management
Data Engineering AI/ML Big Data Data Databases IoT
Software Design and Architecture Cloud Architecture Containers Integration Microservices Performance Security
Coding Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks
Culture and Methodologies
Agile Career Development Methodologies Team Management
Data Engineering
AI/ML Big Data Data Databases IoT
Software Design and Architecture
Cloud Architecture Containers Integration Microservices Performance Security
Coding
Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance
Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks

Last call! Secure your stack and shape the future! Help dev teams across the globe navigate their software supply chain security challenges.

Modernize your data layer. Learn how to design cloud-native database architectures to meet the evolving demands of AI and GenAI workloads.

Releasing software shouldn't be stressful or risky. Learn how to leverage progressive delivery techniques to ensure safer deployments.

Avoid machine learning mistakes and boost model performance! Discover key ML patterns, anti-patterns, data strategies, and more.

Related

  • FHIR Data Model With Couchbase N1QL
  • RION - A Fast, Compact, Versatile Data Format
  • How Trustworthy Is Big Data?
  • Enhancing Avro With Semantic Metadata Using Logical Types

Trending

  • Java's Quiet Revolution: Thriving in the Serverless Kubernetes Era
  • Comparing SaaS vs. PaaS for Kafka and Flink Data Streaming
  • Scalability 101: How to Build, Measure, and Improve It
  • Immutable Secrets Management: A Zero-Trust Approach to Sensitive Data in Containers
  1. DZone
  2. Data Engineering
  3. Data
  4. Different Ways to Search Database Objects

Different Ways to Search Database Objects

This article explains different methods to find the database objects in a SQL Database. We can use any of the mentioned methods to search a database object.

By 
Nisarg Upadhyay user avatar
Nisarg Upadhyay
·
Jan. 17, 23 · Tutorial
Likes (2)
Comment
Save
Tweet
Share
3.6K Views

Join the DZone community and get the full member experience.

Join For Free

This article explains different methods to find the database objects in a SQL Database. We can use any of the following methods to search a database object.

  • Find database objects using system catalog views.
  • Find the database object using the filter option of SQL Server management studio.
  • Find database objects using dbForge SQL Search.

Search Database Objects Using System Catalog Views

You can view the list of the database objects from the SQL Server system catalog views. The system catalog views are used to show the information of the database engine. The catalog views can be used to display the meta-data of the SQL Server database. The catalog views inherit the information from the SQL Server metadata tables. For example, sys.tables view inherits the data from sys.objects catalog view.

Following is the list of most-common dynamic management views that can be used to find a database object.

System catalog view Details of database objects
sys.databases Details of the databases created in a SQL Server instance.
sys.tables Details of the tables created in a specific database.
sys. procedures Details of the stored procedures created in a database.
sys.indexes Details of the stored indexes created in a database.
sys.views Details of the stored views created in a database.
sys.triggers Details of the triggers created in a database.

You can read System catalog views to learn more about SQL Server system catalog views and their usage.

We can use SQL Queries with wild cards (LIKE Operator) to find a specific object in a database. Suppose we want to find a table named Posts from the StackOverflow2010 database. The query is the following: 

 
USE StackOverflow2010
GO
SELECT Name, object_id,schema_id,type,type_desc,create_date,modify_date,durability_desc,temporal_type_desc FROM sys.tables WHERE name ='Posts'


Query Output 

Using System Catalog Views Query Output

Search Database Objects Using the Filter Option in SQL Server Management Studio

We can use the filter option of the SQL Server management studio. For example, I want to find the tables whose name starts with stock, and the schema is WareHouse from the wideworldimportors database. To do that, expand wideworldimportors --> Right-click on Tables --> Filter à Filter settings.

Using the Filter Option in SQL Server Management Studio

A dialog box filter setting opens. In the filter criteria, you can specify any of the following parameters

  • Table Name
  • Schema Name
  • Owner
  • Durability Type
  • Is Memory optimized
  • Creation Date

In our case, the table name has the Stock keyword, and the schema name is Warehouse, so I have selected Contains operator in the table name field and the Equals operator in the Schema field. See the following image.


 Contains operator in the table name field and the Equals operator in the Schema field

Once the filter is applied, you can see the list of tables whose name starts with the Stock keyword. See the following screenshot. 

 the list of tables whose name starts with the Stock keyword.

The above screenshot shows the list of tables whose name starts with the Stock keyword.

Search Database Objects Using dbForge SQL Search for SQL Server

As I explained in the above examples, we can use DMVs and SSMS to filter the objects, but they have a few limitations.

We can search database objects using dbForge Search for SQL Server tool. It is one of the dbForge SQL Tools used to find any database objects or data from the table, increasing the developers' productivity. For example, a developer works on a large database containing thousands of tables. Now it is difficult to remember the name of all tables. In such cases, the dbForge SQL tool can be handy. All we have to do is to provide a few characters of the table name or the initials of the table name. The tool will automatically populate the name of the database object. Let me explain to you with a simple demonstration.

I have restored the wideWorldImportors database. We want to find a stored procedure whose name starts with configuration. Let us understand how we can find it.

First, open SQL Server Management Studio 15.0 --> Expand SQL Server instance --> Expand Databases --> Right-click WideworldImportors --> Hover on Search --> Select Find Object. 


The object search tab opens. The search tab window is split into three sections. The first tab shows the following details.

  • Search Text box: Enter the name or initials of the database objects. The SQL Search will populate the list of objects whose name contains the word/characters specified in a Search text box. In our demo, I specified the configuration keyword.
  • Object Type: You can select any of the following object types:
    1. General: Tables, Views, Columns, Indexes,  Constraints, and synonyms.
    2. Programmability: Stored Procedures, Aggregate functions, Scalar functions, table-valued functions, Triggers, etc.
    3. Service Broker: Message type, Contracts, Queues, Routes, etc.
    4. Storage: Full-text Search, Partition Schemes, Partition Functions.
    5. Security: Users, Roles, Schemas, Asymmetric Keys, Certificate Symmetric keys.  

In our demo, I selected the Stored Procedures.

  • Database Name: Enter the database name. You can see the list of all databases created in a database instance. We are searching for stored procedures in wideworldimportors; therefore, I have selected wideworldimportors from the drop-down list.
  •  Connection Name: Select the hostname.

After specifying all parameters, click on Search to get the list of database objects. See the following image:

 the Devart Search populated all stored procedures whose name contains a keyword along with the following details.

As you can see in the above screenshot, the Devart Search populated all stored procedures whose name contains a keyword along with the following details:

  • Name: Stored procedure name.
  • Owner: Owner of the stored procedure.
  • Schema: Schema in which the procedure is created.
  • Database Name: If the object is found in multiple databases, you can see the database name in which the object is created.
  • Object Type: This column shows the type of database object.
  •  Found In: This column shows that the entered keyword is found in the stored procedure code or name of the stored procedure.

Moreover, when you select any stored procedure, it displays the code of the stored procedure.

Summary

This article explains different ways to search a database object within a SQL Server database.

Database Object (computer science) Big data Data structure Search data structure

Opinions expressed by DZone contributors are their own.

Related

  • FHIR Data Model With Couchbase N1QL
  • RION - A Fast, Compact, Versatile Data Format
  • How Trustworthy Is Big Data?
  • Enhancing Avro With Semantic Metadata Using Logical Types

Partner Resources

×

Comments
Oops! Something Went Wrong

The likes didn't load as expected. Please refresh the page and try again.

ABOUT US

  • About DZone
  • Support and feedback
  • Community research
  • Sitemap

ADVERTISE

  • Advertise with DZone

CONTRIBUTE ON DZONE

  • Article Submission Guidelines
  • Become a Contributor
  • Core Program
  • Visit the Writers' Zone

LEGAL

  • Terms of Service
  • Privacy Policy

CONTACT US

  • 3343 Perimeter Hill Drive
  • Suite 100
  • Nashville, TN 37211
  • support@dzone.com

Let's be friends:

Likes
There are no likes...yet! 👀
Be the first to like this post!
It looks like you're not logged in.
Sign in to see who liked this post!