Identify references of an object in SQL Server

Problem Statement:

Today, there was a requirement from one of my colleague “How to identify a function references in SQL Server?”.

Infact, He was looking for a script to identify the reference places(objects) where the function is being used in SQL Server. Please find the script below that we used to identify the references.

Script:

DECLARE @ObjectName NVARCHAR(100)
SET @ObjectName = N'split' --Give your function/proc name

--Table variable to hold the results from different databases
Declare @ResultTable Table(SourceSchema sysname, Sourceobject sysname, ReferencedDB sysname, ReferencedSchema sysname, ReferencedObject sysname)

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 + '%''';

Insert into @ResultTable
EXEC sp_MSforeachdb  @MyQuery

Select * From @ResultTable

The above script uses sp_MSforeachdb to execute the query in all databases available in the SQL Server.

Please note that sp_MSforeachdb is an undocumented procedure, Microsoft may change the functionality or definition of this Stored Procedure at any time.

See Also:

You may be interested to know more on sp_MSforeachdb usage, please visit the below links

https://gallery.technet.microsoft.com/T-SQL-Script-Get-Table-Row-94e49d01
https://gallery.technet.microsoft.com/How-to-change-datacompressi-47dfce48

Advertisements

PARSENAME in SQL Server: a Powerful function

Introduction:
PARSENAME is one of my favourite functions in SQL Server. But, unfortunately, I could not see many people using this function efficiently.So, I thought of sharing some of its usages here with you.

PARSENAME function can be used to retrive specified part of a string that has four parts separated with DOT(.). This function can be used efficiently to retrive the each part name with four part names like fully qualified objectnames.


--Database four part Object name Usage
create Table Test_ParseName(ObjectName Varchar(100))

Insert into Test_ParseName Values('SER1.DB1.SCH1.TAB1'),('SER2.DB2.SCH2.TAB2')

Select PARSENAME(ObjectName,4) 'SERVERNAME',PARSENAME(ObjectName,3) 'DBNAME',PARSENAME(ObjectName,2) 'SCHEMANAME',PARSENAME(ObjectName,1) 'OBJNAME'
From Test_ParseName

Drop table Test_ParseName

The above is a typical example used in Books online(BOL).

Here, I would like to introduce some more scenarios where we can efficiently use PARSENAME function.

1. To separate IPAdress Sections and for its validation:
Note: IPAddress validation is not a fool-proof solution, however, used to showcase a typical usage of PARSENAME.


create Table Test_ParseName(IPAddress Varchar(100))

Insert into Test_ParseName Values('10.123.123.234'),('10.234.12.123'),('101.123.234.12.123'),('10.234.12'),('10.234.12.')

Select IPAddress,
		PARSENAME(IPAddress,4) 'SECTION1',PARSENAME(IPAddress,3) 'SECTION2',
		PARSENAME(IPAddress,2) 'SECTION3',PARSENAME(IPAddress,1) 'SECTION4',
		/*Another Usage If we want to check the test is satisfying the 4 part naming separated by "."
		Please note that it does not cover the entire validation of IPAdress*/
		Case when (PARSENAME(IPAddress,1) is not null and PARSENAME(IPAddress ,4) is not null) 
				Then 'Satisfied four part' Else 'Invalid format' End
From Test_ParseName

Drop table Test_ParseName

PARSENAME_image2

2. Another example with REPLACE function:
PARSENAME can be used along with REPLACE in many places. Try out the below;the below sample will replace “-” character with “.” and use PARSENAME to separate the four part string easily.


create Table Test_ParseName(string Varchar(100))

Insert into Test_ParseName Values('Check-function-usage-efficiently'),('Check-function-usage'),('Check-function'),('Check'),('Check-function-usage-efficiently-failed')

;With cte_Test_parsename as
(Select REPLACE(string,'-','.') stringmodified From Test_Parsename)

Select stringmodified,
		PARSENAME(stringmodified,4) 'SECTION1',PARSENAME(stringmodified,3) 'SECTION2',
		PARSENAME(stringmodified,2) 'SECTION3',PARSENAME(stringmodified,1) 'SECTION4'
From cte_Test_parsename

Drop table Test_ParseName

PARSENAME_image3

Hope you enjoyed this post. I would like to request you to share any other scenario you come across where PARSENAME can be efficiently used.

Change first character of table names with capital character including intermediate words using sp_rename

How about your collegue asking you to change all of your table names with capital character including intermediate words?

Here is a quick way for you to get it done.

Step 1: You need to create the below function. The below function would change the name that you pass to desired format. The below script is written by George Mastros (gmmastros), a mind-blowing script, may be the fastest for such scenarios.

Create FUNCTION dbo.InitialCap(@String VARCHAR(8000))
RETURNS VARCHAR(8000)
AS
	BEGIN 
	---------------------------------------------------------
	DECLARE @Position INT

	SELECT @String   = STUFF(LOWER(@String),1,1,UPPER(LEFT(@String,1))) COLLATE Latin1_General_Bin,
        @Position = PATINDEX('%[^A-Za-z''][a-z]%',@String COLLATE Latin1_General_Bin)

	WHILE @Position > 0
		SELECT @String   = STUFF(@String,@Position,2,UPPER(SUBSTRING(@String,@Position,2))) COLLATE Latin1_General_Bin,
				@Position = PATINDEX('%[^A-Za-z''][a-z]%',@String COLLATE Latin1_General_Bin)

	---------------------------------------------------------
	RETURN @String;
END 

Step 2: Verify the changes done by the above function. The below script will generate script to rename the tables using sp_rename . sp_rename is a system procedure to change the name of user created objects like tables, index, column or CLR user types. Do remember,when you change the object name, there is a chance of breaking your codes, so be careful!!!

select 'exec sp_rename @objname=[' + name + '], @newname=[' + dbo.InitialCap(name) + ']'
from sysObjects
where type = 'U'

Step 3: Apply the changes once verified.

Declare @s nvarchar(MAX)=''
select @s = @s+'exec sp_rename @objname=[' + name + '], @newname=[' + dbo.InitialCap(name)  +'];' 
from sysObjects
where type = 'U'
--print @s
exec(@s)

Update Table with Random numbers without duplicates in SQL Server

Problem Statement:

Recently, there was a question in one of SQL Server forum asking on updating all table rows with some Random numbers without duplicates. The person who had asked the question could achieve generating the random numbers in a way that there are some duplicates. However, his intention was updating the existing rows with unique random numbers.

Here is the script shared by Questioner


create table RandomNumberUpdate(Col1 bigint)
Go
Insert into RandomNumberUpdate Values(1)
Go 1000

Select * From RandomNumberUpdate

UPDATE RandomNumberUpdate
SET Col1 = CONVERT(int, RAND(CHECKSUM(NEWID())) * 10000)

Once after the update query executes, we can observe there are many duplicates in the table.You may try it yourself and see the duplicates. Use the below query to check the duplicates.


;With cte as
(
Select *,ROW_NUMBER()over(partition by Col1 Order by Col1 asc) Rn From RandomNumberUpdate
)Select* From cte Where rn>1

Solution:

Here is a different approach to get UNIQUE values without duplicates.


UPDATE RandomNumberUpdate
SET Col1 = Abs(Checksum(NewId()))  

Query the table to confirm for any duplicates as below and see that there are no duplicates.


;With cte as
(
Select *,ROW_NUMBER()over(partition by Col1 Order by Col1 asc) Rn From RandomNumberUpdate
)Select* From cte Where rn>1

--Clean the Code

Drop table RandomNumberUpdate

Travel Planner in SQL Server

Everyone needs a travel plan especially if you are working in India, it is tough to reserve your tickets as it needs. I had lot of bad experiences missing my reservations without a well in advance booking – that pays me a lot.

Here, I would like to share with you one of my SQL Server Script prepared for my travel plan.

The idea is very simple – I would love to see my leave dates and the number of days that I would get. I also wanted to look at an option with a voluntary leave to get more holidays. 🙂

The script is very much configurable and easy to understand, so that anyone can change as per the requirement.

The below screenshot is a sample output of the Script:

Train_Calendar

Script:

Declare @LeaveCalendar as Table (Calendar_Date Date,Train_Date Date,WeekName Varchar(10), Is_NationalHoliday Bit,Is_LocalHoliday Bit, Holiday_Description Varchar(100))

Declare @CalendarYear char(4) = '2015'
Declare @BookingPeriod int = 60 --No.Of days prior the ticket needs to be booked.

;With cteCalendar
as
(
 Select DATEADD(yy, DATEDIFF(yy,0,@CalendarYear + '-01-01'), 0) CalendarDate
 Union All
 Select DATEADD(day,1,Calendardate) CalendarDate From cteCalendar
 Where CalendarDate < DATEADD(yy, DATEDIFF(yy,0,@CalendarYear + '-01-01') + 1, -1)
)
Insert into @LeaveCalendar(Calendar_Date,Train_Date,WeekName)
Select CalendarDate,DATEADD(day,-(@BookingPeriod),CalendarDate),DATENAME(WEEKDAY,CalendarDate) From cteCalendar OPTION(MAXRECURSION 366)

--Saturday and Sunday List Holiday update
Update @LeaveCalendar Set Is_NationalHoliday = 1, Holiday_Description = WeekName
Where WeekName in ('Sunday','Saturday')

--National Holiday List
;with cte(sDate,sDescription) as 
(
 Select @CalendarYear+'-01-01' ,'New Year' 
 Union All
 Select @CalendarYear+'-01-26' ,'Republic Day'
 Union All
 Select @CalendarYear+'-05-01' ,'May Day'
 Union All
 Select @CalendarYear+'-10-02' ,'Gandhi Jayanthi'
 Union All
 Select @CalendarYear+'-12-25' ,'Christmas Day'
)
Update A Set Is_NationalHoliday = 1,Holiday_Description = B.sDescription 
From @LeaveCalendar A
Inner join cte B On A.Calendar_Date = B.sDate 

--LocalHoliday List(The below is a sample, you may add your local holidays in the list)
;with cte(sDate,sDescription) as 
(
 Select @CalendarYear+'-01-15' ,'Pongal' 
 Union All
 Select @CalendarYear+'-04-14' ,'Tamil New Year' 
 Union All
 Select @CalendarYear+'-08-20' ,'Onam' 
 Union All
 Select @CalendarYear+'-09-17' ,'Ganesh Chaturthi'
 Union All
 Select @CalendarYear+'-10-21' ,'Ayudha Puja'
 Union All
 Select @CalendarYear+'-11-10' ,'Diwali'

)
Update A Set Is_LocalHoliday = 1,Holiday_Description = B.sDescription 
From @LeaveCalendar A
Inner join cte B On A.Calendar_Date = B.sDate 

;With cte as
(
Select *,Coalesce(Is_NationalHoliday,Is_LocalHoliday) IsHoliday From @LeaveCalendar
)
,cteHolidayPeriod as
(
 Select *,Coalesce(IsHoliday,Case when Lead(IsHoliday,1)Over(Order by Calendar_date) = Lag(IsHoliday,1)Over(Order by Calendar_Date) 
 and Lag(IsHoliday,1)Over(Order by Calendar_Date) =1 Then 1 
 Else 0 End) as 'Holiday_Period_Voluntry',
 Case when Lead(IsHoliday,1)Over(Order by Calendar_date) = Lag(IsHoliday,1)Over(Order by Calendar_Date) 
 and Lag(IsHoliday,1)Over(Order by Calendar_Date) =1 and IsNull(IsHoliday,0) = 0 Then 1 
 Else NULL End Is_VoluntryHoliday
 From cte
)
,cteReadable as
(
 Select 
 *,datediff(day, '19000101', Calendar_Date) - ROW_NUMBER() OVER (order by Calendar_date) as GroupID 
 From cteHolidayPeriod Where Holiday_Period_Voluntry=1
)
Select *,
(Select Top 1 Holiday_Description From @LeaveCalendar Where Calendar_Date between Calendar_HolidayStartDate and Calendar_HolidayEndDate
 and 1 in (Is_LocalHoliday,Is_NationalHoliday) and Weekname not in ('Saturday','Sunday')) 'Holiday Description'
From(
Select 
 Min(Calendar_date) Calendar_HolidayStartDate,Max(Calendar_date) Calendar_HolidayEndDate,
 DateAdd(day,-1,Min(Train_Date)) Booking_HolidayStartDate,Max(Train_Date) Booking_HolidayEndDate,
 Count(Calendar_Date) 'No.Of Holidays', Count(Is_VoluntryHoliday) 'No.Of Voluntry Holiday'
From cteReadable
Group by GroupID having Count(Calendar_Date)>2)A