Find an object name in all databases.

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…!

Advertisements

Find SQL Server deatils

Tags

CONNECTIONPROPERTY, IP Address TCP Port Product Name Edition SP, SERVERPROPERTY, SQL Server details

Sometimes DBA’s need to quickly extract few SQL Server details like
SQL Server Name
Instance Name
IP Address
TCP Port
Product Name
Edition
SP Level
Product Version
Collation

Many a times we extract this information by running multiple queries or toggling multiple windows, this T-SQL script will help you to extract same information through a single execution.
You can download T-SQL script from Microsoft gallery.

Basically in this script I have demonstrated how to use SERVERPROPERTY and CONNECTIONPROPERTY functions available in SQL Server 2008 and higher.

Hope this helps…!

SQL Server – Finding Last Backup & Restore Date for All Database

Tags

last backup, last restore, SQL Server

Many a times we need to find when my database last backed up or when it was last restored.
Below is the quick script i use to find last backup and restore dates.

Script to find last backup date
SELECT
db.Name AS DatabaseName,
CONVERT(VARCHAR(12), MAX(bus.backup_finish_date), 101) AS LastBackUpDate
FROM
sys.sysdatabases db
LEFT JOIN msdb.dbo.backupset bus ON bus.database_name = db.name
GROUP BY
db.Name

Script to find last restored date
SELECT
db.Name AS DatabaseName,
CONVERT(VARCHAR(12), MAX(rh.restore_date), 101) AS LastBackUpDate
FROM
sys.sysdatabases db
LEFT JOIN msdb.dbo.restorehistory rh ON rh.destination_database_name = db.name
GROUP BY db.Name

Hope this helps…!

sp_helptext to view objects on a linked server

Tags

linkedserver, remote sp_helptext, source server, sp_help, sp_helptext

Access object definition of linked server from source server.

Syntax to execute sp_Helptext on Linked server
EXEC [LINKEDSERVER].[DATABASE].dbo.sp_Helptext ‘OBJECTNAME‘
Object name could be procedure,view

Syntax to Execute sp_Help on Linked server
EXEC [LINKEDSERVER].[DATABASE].dbo.sp_Help ‘OBJECTNAME‘
Obejct name could be table, procedure, view

Hope this helps!

SQL Server – Query to find first and last day of any month

Tags

dateadd, datepart, datetime, first and last day of the month in sql, sql datetime, SQL Server datetime

Following query will take date as a input parameter and  returns you first and last days of previous month, current month and next month.

SET NOCOUNT ON
DECLARE @MonthStartDate As datetime
DECLARE @MonthEndDate As datetime
DECLARE @today datetime
SELECT @today = convert(varchar(12) , getdate() , 101) 
--change date as per your requirement

--First & Last dates for previous month
SET @MonthStartDate = @today
SELECT @MonthStartDate = dateadd ( dd , - datepart ( dd , @MonthStartDate ) , @MonthStartDate+1 )
SELECT @MonthStartDate = dateadd(mm , -1 , @MonthStartDate)
SELECT @MonthEndDate = dateadd(dd , -1 , dateadd ( mm , +1 , @MonthStartDate))
SELECT @MonthStartDate AS StartDate , @MonthEndDate AS EndDate

--First & Last dates for current month
SET @MonthStartDate = @today
SELECT @MonthStartDate = dateadd ( dd , - datepart ( dd , @MonthStartDate ) , @MonthStartDate+1 )
SELECT @MonthEndDate = dateadd(dd , -1 , dateadd ( mm , +1 , @MonthStartDate))
SELECT @MonthStartDate AS StartDate , @MonthEndDate AS EndDate

--First & Last dates for next month
SET @MonthStartDate = @today
SELECT @MonthStartDate = dateadd ( dd , - datepart ( dd , @MonthStartDate ) , @MonthStartDate+1 )
SELECT @MonthStartDate = dateadd(mm , +1 , @MonthStartDate)
SELECT @MonthEndDate = dateadd(dd , -1 , dateadd ( mm , +1 , @MonthStartDate))
SELECT @MonthStartDate AS StartDate , @MonthEndDate AS EndDate

Result

Use Convert function to format the out put.
convert(varchar(12) , getdate() , 101) — MM/dd/yyyy
convert(varchar(12) , getdate() , 103) — dd/MM/yyyy

Happy learning.