Found an easier way to do it. (Combined 2 scripts):

select distinct hostname, client_net_address from sysprocesses

inner join sys.dm_exec_connections ON sysprocesses.spid=sys.dm_exec_connections.session_id

where hostname <>...

Awesome script. Works like a clock. Thanks.

You do not need to have a stored procedure there.

Made 3 modifications:

1. Converted it an ad hoc query using the sysprocesses.hostname column

2. Expanded some...

Alberto:

This works fine, but 2 things:

1. It will not work on SQL 2005 as it does not have the sys.dm_sql_referencing_entities view

2. It will not work if the formal references have...

Alberto Turelli (6/24/2015)

---

I happen to have tables with the same name in different schemas (mainly staging.<tablename> and dw.<tablename>), so I tweaked the query a little to display (and use) full...

To the Point #1:

Add/comment the initial SELECT where clause:

where name <> 'TEMPDB'

and (status & 512) = 0 /*online dbs only*/

and (status & 1024) = 0 /*read-write...

Completed with the trace file pathame finder:

IF OBJECT_ID('tempdb..#block_process_reports') is not null

drop table #block_process_reports

declare @TraceFile varchar(100)

--set @TraceFile = 'InsertTraceFileHere'

-- Listing 1. Finding trace file location using sys.traces

select @TraceFile = REVERSE(SUBSTRING(REVERSE(path),CHARINDEX('\',REVERSE(path)),LEN(path)))+'log.trc'

FROM...

Thanks, very useful script.

A few nagging problems:

1. Exclude OFF-LINE databases (good to exclude the READ-ONLY databases as well as u cannot alter them)

2. Expand the following fields as below:

...

Reply to SSC-Enthusiastic:

Nice addition.

Missing a WHERE clause in the inner query:

WHERE CHARINDEX(TableName,Definition,0)<>0

AND a DISTINCT clause.

Amended query:

WITH TableList_CTE (TableName)

AS

(

SELECT TABLE_NAME + CHAR(32) AS TableName

...

Nice Script. Thanks. Fills the missing link.

Added the DISTINCT keyword to the last part as it was giving me duplicates. Otherwise works great.

Amended dcript below:

WITH TableList_CTE (TableName)

AS

(

SELECT TABLE_NAME + CHAR(32)...

Nice script.

Works relatively quick, and could be used for troubleshooting performance issues.

The script was initially failing on all databases with the spaces in their names (common for the SharePoint databases)....

Nadrek (1/9/2014)

---

In at least sys.databases, database name is of SYSNAME type, which currently equates to nvarchar(128) NOT NULL. While I...

Awesome script. Very useful. One little bug in the #logsizestats table definition:

[DBName] varchar(65),

should be

[DBName] varchar(255),

causes string truncation on the sharepoint databases (or any other long...

Nice script.

Already mentioned the spaces in the database names. Fixable with brackets in the USE statements

It has a [dbo] user issue.

Tries to script this:

CREATE USER [dbo] FOR LOGIN [dbo] WITH...

Hi,

Very nice script.

Could be very usefull in certain situations.

The script has multiple issues with the data being truncated (database names specifically)

as well as database names containing dashes (SharePoint...

See the custom SSMS report attached.

Works in SSMS 2005, 2008, 2008R2.

Added interactive sorting on all report colums so that you could se it by database name, by principal name, then...