• About

sqlerrors

~ From the errors of others, a wise man corrects his own

sqlerrors

Monthly Archives: October 2014

Find an object name in all databases.

14 Tuesday Oct 2014

Posted by Praveen D'sa in SQL Script

≈ Leave a comment

Tags

dm_sql_referenced_entities, sp_MSforeachdb, sql_expression_dependencies

I have one user defined function , but i couldn’t find where the function is and where the function is using.. anyone pls help me to overcome this one.?

The below script will help you find all the references within each database.

DECLARE @ObjectName NVARCHAR(100)
SET @ObjectName = N'split' --Give your function/object name
Declare @MyQuery NVARCHAR(MAX) = N'
USE [?]
SELECT DISTINCT
SourceSchema = OBJECT_SCHEMA_NAME(sed.referencing_id)
,SourceObject = OBJECT_NAME(sed.referencing_id)
,ReferencedDB = ISNULL(sre.referenced_database_name, DB_NAME())
,ReferencedSchema = ISNULL(sre.referenced_schema_name,
 OBJECT_SCHEMA_NAME(sed.referencing_id))
,ReferencedObject = sre.referenced_entity_name
FROM sys.sql_expression_dependencies sed
CROSS APPLY sys.dm_sql_referenced_entities(OBJECT_SCHEMA_NAME(sed.referencing_id) + ''.'' + OBJECT_NAME(sed.referencing_id), ''OBJECT'') sre
WHERE sed.referenced_entity_name like ''' + @ObjectName + ''' AND sre.referenced_entity_name like ''' + @ObjectName + '''';

EXEC sp_MSforeachdb  @MyQuery

References

sys.sql_expression_dependencies
sys.dm_sql_referenced_entities

Note

The system procedure sp_MSforeachdb is neither documented nor officially supported by Microsoft.
Hope this helps…!

Share this:

  • More
  • Email
  • Print

SQL Server 2008 SP4 and SQL Server 2008 R2 SP3 have been released

02 Thursday Oct 2014

Posted by Praveen D'sa in SQL Server

≈ Leave a comment

Tags

SQL Server, SQL Server 2008 R2 SP3, SQL Server 2008 SP4, SQL Server Service Pack

Microsoft SQL Server 2008 Service Pack 4 (SP4) and SQL Server 2008 R2 Service Pack 3 (SP3) are now available for download.

Download Service Pack 4 for Microsoft® SQL Server® 2008 

Download Service Pack 3 for Microsoft® SQL Server® 2008 R2

Share this:

  • More
  • Email
  • Print

Recent Posts

  • What’s wrong with this query? July 30, 2015

Categories

  • Learning (2)
  • MS SQL Tips (3)
  • Performance Tuning (1)
  • SQL Script (5)
  • SQL Server (14)
    • Index (1)
    • Log Shipping (1)
  • SQL Tips & Tricks (1)
  • Uncategorized (5)

Archives

  • July 2015 (2)
  • February 2015 (1)
  • October 2014 (2)
  • September 2014 (1)
  • May 2014 (1)
  • April 2014 (3)
  • March 2014 (2)
  • February 2014 (2)
  • January 2014 (1)
  • December 2013 (3)
  • November 2013 (9)

sqlerrors

sqlerrors

Disclaimer:

This is my personal blog. The opinions & suggestions expressed here are
my own. For accuracy and official references please refer to MSDN,Microsoft TechNet,
Books Online. I do not endorse any of the tools / applications / books / concepts
mentioned here on my blog. I have simply documented my personal experiences on this
blog.

Create a free website or blog at WordPress.com.

loading Cancel
Post was not sent - check your email addresses!
Email check failed, please try again
Sorry, your blog cannot share posts by email.
Privacy & Cookies: This site uses cookies. By continuing to use this website, you agree to their use.
To find out more, including how to control cookies, see here: Cookie Policy