TSQL queries-Look into SQL Server Db!!! sysobjects,syscomments,information_schema,sysjobs,sysjobsteps

When I need to change anything in my database or I need to analyze something in it I generally try to play safe and I hope we all try to do so :) !!!
I mean, I research on the flow and dependencies and then will jump on any conclusion/solution. Here I just want to share few T-SQL queries which I use for this purpose. These queries also helps in regression.

First
Query to find presence of particular text in any SQL object. Text can be a table name (in order to find out where all this table is getting used), it can be a procedure or view's name. SQL objects can be procedures, views etc..

SELECT NAME
FROM SYSOBJECTS
WHERE ID IN
(
SELECT ID
FROM SYSCOMMENTS
WHERE TEXT LIKE

'%TEXT GOES HERE%'

)

Second
Query to find out the dependencies between SQL objects. e.g Table A is getting used in View B. Then the below mentioned query will give you Table A's name as output as View B has dependecy on Table A.
Note: if View B is further used in View C and we use View C in below mentioned query we will get only View B as output. Thus, we can conclude that the query will not list indirect dependency.

You can use this queries as a sub-query and tweak the logic if you want to find out indirect dependencies.

SELECT NAME
FROM SYSOBJECTS
WHERE ID IN
(
SELECT
SD.DEPID FROM
SYSOBJECTS SO,
SYSDEPENDS SD
WHERE SO.NAME =

'OBJECT NAME'

AND SD.ID = SO.ID
)

Third
Query to find out Table/View structure. This will give the information regarding the column name, datatype, ordinal position etc.

SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME =

'TABLE/VIEW NAME HERE'

Fourth
This query provides detail information about the sql job. It gives step name, command type, database, command etc..

You can use this queries as a sub-query and tweak the logic if you want to do more with job information. Use MSDB database to run this query.

select
step_id,
step_name,
subsystem,
case on_success_action
when 3 then 'Goto next step '
when 1 then 'Quit with Success'
when 2 then 'Quit with Failure'
when 4 then 'Goto step '

end +

case convert(varchar(1),on_success_step_id)
when 0 then ''
else convert(varchar(1),on_success_step_id)
end as on_success,

case on_fail_action
when 3 then 'Goto next step '
when 1 then 'Quit with Success'
when 2 then 'Quit with Failure'
when 4 then 'Goto step '
end + convert(varchar(3),
case convert(varchar(1),on_fail_step_id)
when 0 then ''
else convert(varchar(1),on_fail_step_id)
end) as on_failure,

isnull(database_name,''),
command
from sysjobsteps sjs
inner join sysjobs sj
on sj.job_id = sjs.job_id
where sj.name =

'JOB NAME GOES HERE'

Hope this will help!!!