Search columns in all tables in database
Here I am describing about how we can find/search one column in a database
We will be confused to find the same in some situation. So Here I am writing the solution with the query
We can use this query in a stored procedure.
Here I am describing about how we can find/search one column in a database
We will be confused to find the same in some situation. So Here I am writing the solution with the query
We can use this query in a stored procedure.
Suppose we have a DB called School. And we have so many tables and stored procedures are there.
1. Query to find how many tables created under the database
select * from sys.tables order by name
2. Query to find how many stored procedures created under the database
select * from sys.procedures order by name
other than this can find foreignkeys used , schemas,databases etc.
3.Now we can find/Search the columns in the database in two ways.
We can find the column TeacherId, which is used in the database, how many tables used the column 'TeacherId'
SELECT allTable.name AS tableName,
Tablecolumn.name AS columnName
FROM sys.tables AS allTable
INNER JOIN sys.columns Tablecolumn ON allTable.OBJECT_ID = Tablecolumn.OBJECT_ID
WHERE Tablecolumn.name LIKE '%TeacherId%'
ORDER BY tableName;
Or
select table_name,column_name from information_schema.columns
where column_name like '%TeacherId%' ORDER BY table_Name;
Here we used %TeacherId%, this will result all teacherId used in the database with table name.
Output will be
table_name column_name
Teacher_tbl TeacherId
Student_Tbl TeacherId
Classteacher_tbl ClassteacherId
Like that if we used 'TeachedId%' search result starts with TeacherId end may be different