Requirement :
How to find the Object dependency over TABLE either or PROCEDURE, FUNCTIONS, VIEWS etc...
Solution :
declare @objectName varchar(max)
set @objectName = 'your objects name here...'
select distinct
object_name(a.depid) TableName, object_name(a.id) DependentOn, b.xtype
from
sysdepends as a
inner join sysobjects as b on a.id = b.id
where
(a.depid = object_id(@objectName) or a.id = object_id(@objectName));
Result :
======================================================================
Requirement :
How to retrieve HDD space on server.
below is the system stored procedure to get the HDD space on server :
EXEC master..xp_fixeddrives
Result :
======================================================================
Requirement :
How to get customized week as per the business requirement
Answer :
Here is the function to achieve the this :
What we are doing here is creating the UDF to get the "WEEK". Just simply copy the code and paste in SQL Management Studio and run it or you can customized the code as per your requirement.
Just pass the date to the function, you'll get the desire result
create function fn_GetWeek (@date datetime)
returns nvarchar(20)
as
begin
declare @dayint int
select @dayint = day(@date)
declare @result nvarchar(50)
set @result = (select
case
when @dayint > 21 then 'Week4'
when @dayint <= 21 and @dayint >= 15 then 'Week3'
when @dayint <= 14 and @dayint > 07 then 'Week2' else 'Week1' end as [week])
return @result
end
Result :
======================================================================
Requirement :
Sometime what happen, In corporate user wants the reports on Financial Year Calendar Quarter, So to achieve this I build as a FUNCTION.
Solution :
Here is the function to achieve the same :
Just create the function and pass the date to the function. You'll get the desire result.
create Function fn_FYQtrCal (
@date datetime
)
returns varchar(25)
as
Begin
declare @Qtr varchar(25)
set @Qtr =
(select distinct
coalesce (
case when month(@date) in (4,5,6) then 'Q1 ' + cast(year(@date) as varchar(50)) + '-' + cast(year(@date)+1 as varchar(50)) end,
case when month(@date) in (7,8,9) then 'Q2 ' + cast(year(@date) as varchar(50)) + '-' + cast(year(@date)+1 as varchar(50)) end,
case when month(@date) in (10,11,12) then 'Q3 ' + cast(year(@date) as varchar(50)) + '-' + cast(year(@date)+1 as varchar(50)) end,
case when month(@date) in (1,2,3) then 'Q4 ' + cast(year(@date)-1 as varchar(50)) + '-' + cast(year(@date) as varchar(50)) end) as Qtr
)
return @Qtr
End
Result :
======================================================================
Requirement :
In some scenario we want the age of particular (might be an employee in and organisation, might be a student in the school) in Year, Month and Day...
Solution :
Here is the simple query to achieve the same thing :
DECLARE @date datetime, @tmpdate datetime, @years int, @months int, @days int
SELECT @date = '1986-04-07'
SELECT @tmpdate = @date
SELECT @years = DATEDIFF(yy, @tmpdate, GETDATE()) - CASE WHEN (MONTH(@date) > MONTH(GETDATE())) OR (MONTH(@date) = MONTH(GETDATE()) AND DAY(@date) > DAY(GETDATE())) THEN 1 ELSE 0 END
SELECT @tmpdate = DATEADD(yy, @years, @tmpdate)
SELECT @months = DATEDIFF(m, @tmpdate, GETDATE()) - CASE WHEN DAY(@date) > DAY(GETDATE()) THEN 1 ELSE 0 END
SELECT @tmpdate = DATEADD(m, @months, @tmpdate)
SELECT @days = DATEDIFF(d, @tmpdate, GETDATE())
SELECT @years as [Year], @months as [Month], @days as [Day]
Result :
======================================================================
Requirement :
Some we get the situation where we need to remove the special character(s) from the string.
Solution :
Below is the UDF to achieve the goal :
CREATE FUNCTION DBO.fnRemoveSpecialChars (@S VARCHAR(256)) RETURNS VARCHAR(256)
WITH SCHEMABINDING
BEGIN
IF @S IS NULL
RETURN NULL
DECLARE @S2 VARCHAR(256)
SET @S2 = ''
DECLARE @L INT
SET @L = LEN(@S)
DECLARE @P INT
SET @P = 1
WHILE @P <= @L
BEGIN
DECLARE @C INT
SET @C = ASCII(SUBSTRING(@S, @P, 1))
IF @C BETWEEN 48 AND 57 OR @C BETWEEN 65 AND 90 OR @C BETWEEN 97 AND 122
SET @S2 = @S2 + CHAR(@C)
SET @P = @P + 1
END
IF LEN(@S2) = 0
RETURN NULL
RETURN @S2
END
Result :
you can see the below result, as the select query having the "*". but after using the function "*" has been removed from the result.
======================================================================
Requirement :
Some time we need to check which query is running on SQL, and who is running that query (whether it is running by the system, scheduler or any person).
Solution :
Below is the simple query to see the resule :
select distinct
rtrim(ltrim(sp.hostname)) as hostname,
rtrim(ltrim(sp.loginame)) as loginame,
r.session_id,
r.blocking_session_id as blkby,
r.start_time as starttime,
elapsedmin = datediff(minute,r.start_time,getdate()),
commandtype = r.command,
object_name(st.objectid) as [object_name],
substring(st.text, ( r.statement_start_offset / 2 ) + 1,
( ( case when r.statement_end_offset <= 0
then datalength(st.text)
else r.statement_end_offset end -
r.statement_start_offset ) / 2 ) + 1) as statement_text,
sqlstatement = st.text,
protocol = con.net_transport,
clientaddress = con.client_net_address,
authentication = con.auth_scheme
from sys.dm_exec_requests r
cross apply sys.dm_exec_sql_text(sql_handle) st
inner join (select distinct spid, hostname, loginame from sys.sysprocesses) as sp on r.session_id = sp.spid
left join sys.dm_exec_connections con on con.session_id = r.session_id
where sp.loginame <> '' AND r.session_id <> @@spid
order by datediff(minute,r.start_time,getdate()) desc
Result :
======================================================================
Requirement :
Some time we get the situation that we pass the string value with comma separated (,) or any other separator like '^' or may like '~' and then we need the result as converted to the column.
So to achieve that situation, created the function below :
Solution :
CREATE FUNCTION fn_SplitString
(
@Input NVARCHAR(MAX),
@Character CHAR(1)
)
RETURNS @Output TABLE (
pListId INT IDENTITY(1,1),
pListValue NVARCHAR(1000)
)
AS
BEGIN
DECLARE @StartIndex INT, @EndIndex INT
SET @StartIndex = 1
IF SUBSTRING(@Input, LEN(@Input) - 1, LEN(@Input)) <> @Character
BEGIN
SET @Input = @Input + @Character
END
WHILE CHARINDEX(@Character, @Input) > 0
BEGIN
SET @EndIndex = CHARINDEX(@Character, @Input)
INSERT INTO @Output(pListValue)
SELECT SUBSTRING(@Input, @StartIndex, @EndIndex - 1)
SET @Input = SUBSTRING(@Input, @EndIndex + 1, LEN(@Input))
END
RETURN
END
Result :
======================================================================
