• About

sqlerrors

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

sqlerrors

Category Archives: SQL Script

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

Advertisement

Share this:

  • More
  • Email
  • Print

Like this:

Like Loading...

Find SQL Server deatils

31 Monday Mar 2014

Posted by Praveen D'sa in SQL Script, SQL Server

≈ Leave a comment

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

Share this:

  • More
  • Email
  • Print

Like this:

Like Loading...

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

10 Monday Mar 2014

Posted by Praveen D'sa in MS SQL Tips, SQL Script

≈ Leave a comment

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

Share this:

  • More
  • Email
  • Print

Like this:

Like Loading...

sp_helptext to view objects on a linked server

20 Wednesday Nov 2013

Posted by Praveen D'sa in SQL Script

≈ Leave a comment

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!

Share this:

  • More
  • Email
  • Print

Like this:

Like Loading...

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

14 Thursday Nov 2013

Posted by Praveen D'sa in SQL Script, SQL Server

≈ 1 Comment

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
first and last day of month - sql server

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.

Share this:

  • More
  • Email
  • Print

Like this:

Like Loading...

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.

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
  • Follow Following
    • sqlerrors
    • Already have a WordPress.com account? Log in now.
    • sqlerrors
    • Customize
    • Follow Following
    • Sign up
    • Log in
    • Report this content
    • View site in Reader
    • Manage subscriptions
    • Collapse this bar
 

Loading Comments...
 

    %d bloggers like this: