SQL User Friendly - Developer Friendly Queries
This article explains the list of user friendly/developer queries like selecting list of tables starting with specific letter, Searching for specific column in entire database, searching Sp, text and parameter names.
Introduction
In this article, I would like to give the most user friendly queries that is available in SQL to perform following search operation.
1. List the Table name that contains specific letters/words.
2. List the Column name that contains specific letters with Table Name known or unknown.
3. List the Table and all its foreign key references.
4. List the SP (Stored Procedure) Names that contains specific text.
5. List the Parameters of specific SP (Stored Procedure).
6. List the SP (Stored Procedure) Names that contains specific Parameter.
7. List the user defined tables that contains specific letters/words.
8. List the columns of user defined tables that contains specific letters/words
List the Table name that contains specific letters/words
-- ObjectName
Select * from sys.tables where name like '%OBJECTNAME_HERE%'
List the Column name that contains specific letters with Table Name known or unknown
--Select Query to search Tables and columns
select Tab.name, Cols.name from sys.tables Tab, sys.columns Cols
where Tab.object_id = Cols.object_id
and Tab.name like '%TABLE_NAME_HERE%'
and Cols.name like '%COLUMN_NAME_HERE%'
List the Table and all its foreign key references
--Search foreign Key Columns reference.
Select Parenttable.Name as ParentTable, Cons.Name as constraintName, FKtable.Name as ReferencedTableName , FKCol.Name as FKColumn
from
sys.objects Cons with (nolock)
INNER JOIN sys.foreign_key_columns ConsMaster with (nolock) ON ConsMaster.constraint_object_ID = Cons.object_ID
INNER JOIN sys.objects Parenttable with (nolock) ON Parenttable.object_ID = ConsMaster.Referenced_object_ID
INNER JOIN sys.objects FKtable with (nolock) ON FKtable.object_ID = ConsMaster.Parent_object_ID
INNER JOIN sys.columns FKCol with (nolock) ON FKCol.object_id =ConsMaster.parent_object_id and FKCol.column_id = ConsMaster.parent_column_id
Where Parenttable.Name like '%PARENT_TABLE_HERE%'
Order by 1
List the SP (Stored Procedure) Names that contains specific text
--Select Query to search Text in SP
select distinct sp.name from sysobjects sp, syscomments b
where sp.id = b.id
and sp.name like '%SP_NAME_HERE%'
and b.text like '%TEXT_TO_BE_SEARCHED_HERE%'
List the Parameters of specific SP (Stored Procedure)
-- SP Name and Parameters
Select SP.Name, Params.Name, Params.Parameter_ID, is_output,Params.max_length from sysobjects SP inner join sys.parameters Params on SP.id = Params.Object_id
and SP.Name= 'SP_NAME_HERE'
order by Params.Parameter_ID
List the SP (Stored Procedure) Names that contains specific Parameter
-- To Find the parameters name that is used in different SP
Select SP.Name, Params.Name, Params.Parameter_ID, is_output,Params.max_length from sysobjects SP inner join sys.parameters Params on SP.id = Params.Object_id
and Params.Name= 'PARAM_NAME_HERE'
order by Params.Parameter_ID
List the user defined tables that contains specific letters/words
--User Defined Tables
Select * from sys.objects where name like '%TVP_NAME_HERE%' and type= 'TT'
List the columns of user defined tables that contains specific letters/words
-- Same Cols Selection query can be used to Column List
select Tab.name, Cols.name from sys.objects Tab, sys.columns Cols
where Tab.object_id = Cols.object_id
and Tab.name like '%TVP_NAME_HERE%'
and Cols.name like '%%'