Find Object reference across Database

Don't you always want to know where a table or database base object is been used... Well here it is... enjoy..
Use DatabasNameHere;
go
Set transaction isolation level read uncommitted;
set nocount on;

declare @TableName varchar(80) = 'TableNameGoesHere'
declare @TableSchema varchar(50) = 'TableSchemaGoesHere'
declare @objectid bigint = object_id(@TableSchema+'.'+@TableName);


;with cte_refe as
(
SELECT 
    OBJECT_SCHEMA_NAME ( referencing_id ) AS referencing_schema_name,
    OBJECT_NAME(referencing_id) AS referencing_entity_name, 
object_name(o.Parent_object_id) as Parent_Name,
    o.type_desc AS referencing_desciption, 
    COALESCE(COL_NAME(referencing_id, referencing_minor_id), '(n/a)') AS referencing_minor_id, 
    referencing_class_desc, referenced_class_desc,
    referenced_server_name, referenced_database_name, referenced_schema_name,
    referenced_entity_name, 
    COALESCE(COL_NAME(referenced_id, referenced_minor_id), '(n/a)') AS referenced_column_name,
    is_caller_dependent, 
is_ambiguous,
is_replicated,
o.is_published,
m.definition

FROM sys.sql_expression_dependencies AS sed
INNER JOIN sys.objects AS o ON sed.referencing_id = o.object_id
inner join sys.tables t on t.object_id = @objectid
left outer join sys.all_sql_modules m
on (m.object_id = sed.referencing_id) 

WHERE  ((@objectid = referenced_id) or (@objectid = o.parent_object_id))
--order by 3,1,2
)
select distinct 
       referencing_desciption
  ,Parent_Name
 ,isnull (referencing_schema_name,'dbo')+'.'+referencing_entity_name referencing_entity_name
 ,isnull (referenced_schema_name,'dbo')+'.'+referenced_entity_name As TableName
-- ,referenced_server_name
 ,@@SERVERNAME ServerName
,is_caller_dependent 
  ,is_ambiguous
,is_replicated
,is_published
,definition
 
from
cte_refe
where referencing_entity_name  not like '%syncobj_0%'
--where referenced_server_name is not null
order by 1 desc,2

Search All objects that reference a table or object

Lately I have been searching and analyzing many of our systems, so, as been a lazy programmer here is a script to search any object within sql sever 2008 and above and get all the references that are using that table, procedure, view etc...

Here it is...
use AdventureWorks2012
go
set transaction isolation level read uncommitted;
set nocount on;

declare @TableList table (ObjectID bigint,Table_Schema varchar(50),Table_Name varchar(80))

insert into @TableList(objectid,Table_Schema,Table_Name)
select
 object_id(Table_Schema+'.'+Table_name),TABLE_SCHEMA,TABLE_NAME
from INFORMATION_SCHEMA.tables where TABLE_SCHEMA = 'Person'

SELECT 
    OBJECT_SCHEMA_NAME ( referencing_id ) AS referencing_schema_name
    ,OBJECT_NAME(referencing_id) AS referencing_entity_name 
    ,o.type_desc AS referencing_desciption 
    ,COALESCE(COL_NAME(referencing_id, referencing_minor_id), '(n/a)') AS referencing_minor_id
    ,referencing_class_desc
, referenced_class_desc
    ,referenced_server_name
, referenced_database_name
,referenced_schema_name
    ,referenced_entity_name
,object_NAME (parent_object_id) as Parent_Name
    ,COALESCE(COL_NAME(referenced_id, referenced_minor_id), '(n/a)') AS referenced_column_name
    ,is_caller_dependent
,is_ambiguous
FROM 
sys.sql_expression_dependencies AS sed
INNER JOIN sys.objects AS o 
ON sed.referencing_id = o.object_id
INNER JOIN @TableList tcs 
ON ((referenced_id = tcs.objectid) or (parent_object_id = tcs.ObjectID))
order by 3 desc

SSIS Remove Special Characters For All Columns

/* Microsoft SQL Server Integration Services Script Component
*  Write scripts using Microsoft Visual C# 2008.
*  ScriptMain is the entry point class of the script.*/

using System;
using System.Data;
using System.Text.RegularExpressions;
using Microsoft.SqlServer.Dts.Pipeline;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using Microsoft.SqlServer.Dts.Runtime.Wrapper;

[Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute]
public class ScriptMain : UserComponent
{

    public override void PreExecute()
    {
        base.PreExecute();
        /*
          Add your code here for preprocessing or remove if not needed
        */
    }

    public override void PostExecute()
    {
        base.PostExecute();
        /*
          Add your code here for postprocessing or remove if not needed
          You can set read/write variables here, for example:
          Variables.MyIntVar = 100
        */
    }

    public override void Input0_ProcessInputRow(Input0Buffer Row)
    {
        //Row.AltImages = RemoveCharacter(Row.AltImages);
        //Row.Brand = RemoveCharacter(Row.Brand);
        //Row.Bullets = RemoveCharacter(Row.Bullets);
        //Row.CategorizationDetails= RemoveCharacter(Row.CategorizationDetails);
        //Row.Color = RemoveCharacter(Row.Color);
        //Row.Gender = RemoveCharacter(Row.Gender);
        //Row.League = RemoveCharacter(Row.League);
        //Row.PlayerName = RemoveCharacter(Row.PlayerName);
        //Row.Team = RemoveCharacter(Row.Team);
        //Row.ProductName = RemoveCharacter(Row.ProductName);
        //Row.Vendor = RemoveCharacter(Row.Vendor);
        //Row.DCS = RemoveCharacter(Row.DCS);
        //Row.MCS = RemoveCharacter(Row.MCS);
        //Row.GenderAgeGroup = RemoveCharacter(Row.GenderAgeGroup);
        //Row.TeamTypes = RemoveCharacter(Row.TeamTypes);
        //Row.Sport = RemoveCharacter(Row.Sport);
        //Row.ProductURL = RemoveCharacter(Row.ProductURL);
        //Row.AltImageURLs = RemoveCharacter(Row.AltImageURLs);
        //Row.partnerSiteName = RemoveCharacter(Row.partnerSiteName);
        //Row.KeyWords = RemoveCharacter(Row.KeyWords);
        //Row.PlayerNameAndNumber = RemoveCharacter(Row.PlayerNameAndNumber);


    }


    public override void ProcessInput(int InputID, PipelineBuffer Buffer)
    {
        string pattern = "[^ -~]*";
        Regex reg_exp = new Regex(pattern);

        //bool fireAgain = false;
        //ComponentMetaData.FireInformation(0, "",
        //  Buffer.ColumnCount.ToString() + " columns",
        //  "", 0, ref fireAgain);
        int index = 0;
        String DataBackup = "";
        while (Buffer.NextRow())
        {
            try
            {
                for (int columnIndex = 0;
                  columnIndex < Buffer.ColumnCount;
                  columnIndex++)
                {
                    index = columnIndex;

                    string columnData = null;
                    if (Buffer.IsNull(columnIndex))
                    {
                        columnData = "is NULL";
                    }
                    else
                    {
                        BufferColumn columnInfo = Buffer.GetColumnInfo(columnIndex);

                        switch (columnInfo.DataType)
                        {
                            case DataType.DT_WSTR:
                            case DataType.DT_STR:
                                columnData += Buffer.GetString(columnIndex);
                                DataBackup = columnData;//Save backup
                                columnData = reg_exp.Replace(columnData, "");
                                Buffer.SetString(columnIndex, columnData);
                                index = 0; //Reset index;
                                DataBackup = ""; //Reset data
                                break;
                            // add code to support more data types here

                            default:
                                columnData = "";
                                index = 0; //Reset index;
                                DataBackup = ""; //Reset data
                                break;
                        }
                    }
                    //ComponentMetaData.FireInformation(0, "",
                    //  "Column " + columnIndex.ToString() + ": " + columnData,
                    //  "", 0, ref fireAgain);
                }
            }
            catch
            {
                Buffer.SetString(index, DataBackup);
                index = 0;
                DataBackup = "";
            }
        }
        base.ProcessInput(InputID, Buffer);
    }

}