SqlServer | Oracle equivalent DBA tables

I'm a hard-core ORACLE programmer and lately I had to work with SQL server for a specific project. First thing I noticed is that its a bit hard to search for column names, table_names (*across all databases*) etc in sql server.

In oracle, we have the DBA views like DBA_tables, DBA_tab_columns etc, which are very handy for these purposes.

In SQL sever we have a stored proc called "sp_msforeachdb" to somewhat do this task. But it opens up so many result windows and also creates one window for each DB, even if the DB doesnt have any results.

So I ended up making few stored proc which will work somewhat similar to the oracle's DBA views.

Use the below script to create the working tables.

create table my_dba_tables( db sysname, table_name sysname, object_id int);

create table my_dba_tab_columns( db sysname, table_name sysname, object_id int, table_column sysname);

create table my_dba_objects( db sysname, object_name sysname, object_id int);

Download the stored proc from the below link and run it in your environment-

1. sp_my_get_dba_objects.sql
2. sp_my_get_dba_tab_columns.sql
3. sp_my_get_dba_tables.sql

This is how you execute it ( you can use like or = or anything that a where clause will accept)

dbo.sp_my_get_dba_objects 'name like "%<object_name>%"'

dbo.sp_my_get_dba_tables 'name = "<table_name>"'

dbo.sp_my_get_dba_tab_columns 'name like "%<column_name>%"'