Know the ID is used in Referenced tables--SqlServer

CREATE VIEW VW_ISREFERENCED_LOC
SELECT
LEVELOFCAREID,
SHORTDESCRIPTION,
CODE,
CAST(
CASE WHEN EXISTS( SELECT 'X' FROM CHILDACTIVITY WHERE CHILDACTIVITY.LevelOfCareID IS NOT NULL AND CHILDACTIVITY.LevelOfCareID = LEVELOFCARE.LEVELOFCAREID) THEN 1
WHEN EXISTS( SELECT 'X' FROM CHILDACTIVITYHISTORY WHERE CHILDACTIVITYHISTORY.LevelOfCareID IS NOT NULL AND CHILDACTIVITYHISTORY.LevelOfCareID = LEVELOFCARE.LEVELOFCAREID) THEN 1
WHEN EXISTS( SELECT 'X' FROM SHELTER WHERE SHELTER.LevelOfCareID IS NOT NULL AND SHELTER.LevelOfCareID = LEVELOFCARE.LEVELOFCAREID) THEN 1
WHEN EXISTS( SELECT 'X' FROM SHELTERINQUIRY WHERE SHELTERINQUIRY.LevelOfCareID IS NOT NULL AND SHELTERINQUIRY.LevelOfCareID = LEVELOFCARE.LEVELOFCAREID) THEN 1
WHEN EXISTS( SELECT 'X' FROM RATE WHERE RATE.LevelOfCareID IS NOT NULL AND RATE.LevelOfCareID = LEVELOFCARE.LEVELOFCAREID) THEN 1
WHEN EXISTS( SELECT 'X' FROM RATEHISTORY WHERE RATEHISTORY.LevelOfCareID IS NOT NULL AND RATEHISTORY.LevelOfCareID = LEVELOFCARE.LEVELOFCAREID) THEN 1
WHEN EXISTS( SELECT 'X' FROM SHELTERHISTORY WHERE SHELTERHISTORY.LevelOfCareID IS NOT NULL AND SHELTERHISTORY.LevelOfCareID = LEVELOFCARE.LEVELOFCAREID) THEN 1
WHEN EXISTS( SELECT 'X' FROM FOSTERCHILD WHERE FOSTERCHILD.LevelOfCareID IS NOT NULL AND FOSTERCHILD.LevelOfCareID = LEVELOFCARE.LEVELOFCAREID) THEN 1
WHEN EXISTS( SELECT 'X' FROM FOSTERCHILDHISTORY WHERE FOSTERCHILDHISTORY.LevelOfCareID IS NOT NULL AND FOSTERCHILDHISTORY.LevelOfCareID = LEVELOFCARE.LEVELOFCAREID) THEN 1
WHEN EXISTS( SELECT 'X' FROM FINANCIALPOSTINGDETAIL WHERE FINANCIALPOSTINGDETAIL.LevelOfCareID IS NOT NULL AND FINANCIALPOSTINGDETAIL.LevelOfCareID = LEVELOFCARE.LEVELOFCAREID) THEN 1
WHEN EXISTS( SELECT 'X' FROM MENTALSERVICEREQUEST WHERE MENTALSERVICEREQUEST.LevelOfCareID IS NOT NULL AND MENTALSERVICEREQUEST.LevelOfCareID = LEVELOFCARE.LEVELOFCAREID) THEN 1
ELSE 0 END AS INT) AS IsBeingReferenced
FROM LEVELOFCARE

SqlServer

Dispaly the years&rem months when the col is int datatype
----------------------------------------------------

select cast((age1/12) as varchar(20)) +' yrs ' + cast((age1%12) as varchar(20)) + ' months' from age


when col is varchar
-------------------

select cast( (substring(age1,1,2)/12) as varchar(20)) + ' yrs ' +
cast( (substring(age1,1,2)%12) as varchar(20)) + ' months' from age

code when the Months are zero i.e sdn't display when the months are zero
------------------------------------------------------------------------
select AGeDet.years + AGeDet.age1 from
(
select cast(age1/12 as varchar(30)) + ' years ' years ,+ case age1%12
when 0 then ''
ELSE cast(age1%12 as varchar(10))+ ' months'
END AS age1
from age ) AGeDet



Below code used for preparing the Insert script for the Existing rows in a table
----------------------------------------------------------------------------------
Declare @qt as varchar(1000)
set @qt = ''''
print @qt
select 'insert into program values ',
ProgramID, ',', ProgramCode ,',', @qt+ Description + @qt from program


Script used to display the vertical rows as Horizantal rows by comma separated
------------------------------------------------------------------------------

declare @retstr varchar(8000)
select @retstr = COALESCE(@retstr + ',','') + cast(memno as varchar) from sample where sno=1
print @retstr




For updating the column by removing the chars 'September 2004' can be updated as sept 04 of a single col
--------------------------------------------------------------------------------------------------------
update #t1 set period = left(period,4) + ' ' + right(period,2) where Period like '%sept%'



Supply a Date and it Returns with the Month Last Date by considering the Year i.e if its Leap Year
---------------------------------------------------------------------------------------------------

declare @date datetime
set @date = '02/01/2004'
set @date = getdate()

--get first day of month
select convert(varchar(50),dateadd(m, datediff(m, 0, @date), 0),101) as hi

--get last day of month
select convert(varchar(50),dateadd(m, datediff(m, 0, dateadd(m, 1, @date)), -1),101) as enddate