Alternatives · Basic Logics · Real Scenario Logic's

Easy Like Search to find Table, View, Stored Procedure and Function Names in SQL Server

Introduction

In this blog post, will see how can we easily find or search Table, View and Stored Procedure, Function names in SQL Server.

SQL Script for Sample Table, View, SP, Function Creation

-- Sample Table Creation
CREATE TABLE TestTable
(
TestTable_key INT IDENTITY(1,1) NOT NULL,
TestTable_Name VARCHAR(100) NOT NULL
);
GO
-- Sample View Creation
CREATE VIEW TestView
AS
SELECT TestTable_key,TestTable_Name
FROM TestTable;
GO
-- Sample SP Creation
CREATE PROCEDURE TestProcedure
(
@TestTable_key INT
)
AS
BEGIN
SELECT TestTable_key,TestTable_Name
FROM TestTable WHERE TestTable_key=@TestTable_key;
END;
GO
-- Sample Function Creation
CREATE FUNCTION TestFunctionDate()
RETURNS DATETIME
AS
BEGIN
	DECLARE @Current_DateTime DATETIME;
	SELECT @Current_DateTime=SYSDATETIMEOFFSET() AT TIME ZONE 'India Standard Time';  -- IST Format
	RETURN @Current_DateTime ;
END;
GO

To Like Search Table Names

SQL Script

--To Like Search Table Names
EXEC sp_tables '%TESTT%';

Image Pic

To Like Search Table Names

To Like Search View Names

SQL Script

--To Like Search View Names
EXEC sp_tables '%TESTV%';

Image Pic

To Like Search View Names

To Like Search SP Names

SQL Script

--To Like Search Stored procedure Names
EXEC sp_stored_procedures '%TESTP%';

Image Pic

To Like Search SP Names

To Like Search Function Names

SQL Script

--To Like Search Function Names
EXEC sp_stored_procedures '%TESTF%';

Image Pic

To Like Search Function Names

NOTE

sp_tables and sp_stored_procedures are the T-SQL Catalog Stored Procedures. sp_tables returns a list of objects(Tables and Views) that can be queried in the current environment. sp_stored_procedures returns a list of stored procedures and functions in the current environment.

While Using sp_stored_procedures, the Procedure_Name output field returns Stored Procedure/Function Name , separator(; [semicolon] is used as separator) and 1/0 where as 1 means Stored Procedure and 0 means Function.

SQL Script – To Return Full list of Tables, Views, Sp’s and Functions Using sp_tables and sp_stored_procedures

--To Return full list of Tables and Views
EXEC sp_tables;
--To Return full list of Sp's and Functions
EXEC sp_stored_procedures;

To Like Search Using sys.objects

We can also find all these Tables, Views, Stored Procedures and Functions under sys.objects like querying below.

SQL Script

SELECT NAME,type,type_desc FROM sys.objects WHERE TYPE IN ('U','V','P','FN')
AND NAME LIKE '%TEST%'

Image Pic

To Like Search Using sys.objects

SQL Script – To Return Full list of Tables, Views, Sp’s and Functions Using sys.objects

SELECT * FROM sys.objects WHERE TYPE in ('U','V','P','FN');

Conclusion

Thus, we saw how to easily search Table, View and Stored Procedure, Function names in SQL Server. This works fine in both Azure SQL DB and On-premises. Unit Tested as well.

Thanks for reading and Follow Blog and Show your Support for many more interesting upcoming Posts!

Advertisement

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s