Category Archives: Sql Server

In this article I will explain some general purpose queries. I think each developer should have knowledge of these queries. These queries are not related to any specific topic of SQL. But knowledge of such queries can solve some complex tasks and may be used in many scenarios, so I decided to write an article…

SELECT SCH.name AS SchemaName ,OBJ.name AS ObjName ,OBJ.type_desc AS ObjType ,INDX.name AS IndexName ,INDX.type_desc AS IndexType ,PART.partition_number AS PartitionNumber ,PART.rows AS PartitionRows ,STAT.row_count AS StatRowCount ,cast(((STAT.used_page_count * 8)/1000000) as decimal(18,2)) AS UsedSizegb, cast(((STAT.used_page_count * 8)/1000) as decimal(18,2)) AS UsedSizemb, cast(((STAT.reserved_page_count * 8)/1000000) as decimal(18,2)) AS RevervedSizegb, cast(((STAT.reserved_page_count * 8)/1000) as decimal(18,2)) AS RevervedSizeMb, STAT.reserved_page_count *…

USE YourDB GO –get the list of keys in current adtabase select * from sys.symmetric_keys –get the list of certificates in current database select * from sys.certificates –To store encrypted data in the table you have to change the datatype to varbinary(256) –set the database level encryption password CREATE MASTER KEY ENCRYPTION BY PASSWORD =…

Some time we have to insert in a foreign key reference table where we have to check subcategory id along with main category id for that we can use CHECK CONSTRAINT like below 1)First create a user defined function which will check main category id and sub category id from a view. /****** Object: UserDefinedFunction…

ALTER function [dbo].[fn_LastChild_Select] ( @ParentId int ) returns int as BEGIN Declare @childid int Declare @Id int SET @Id=@ParentId WHILE EXISTS(select * from dbo.miscDemographics_Relation where miscDemographics_MasterId=@ParentId) BEGIN SELECT @ParentId=miscDemographics_Id,@childid=miscDemographics_Id FROM dbo.miscDemographics_Relation where ISNULL(miscDemographics_MasterId,0)=@ParentId END SET @childid=ISNULL(@childid,@Id) return @childid END…

INSERT INTO dbo.miscDemographics_Relation(miscDemographics_Id,miscDemographics_MasterId,miscDemographics_CreatedDate) SELECT i.miscDemographics_ID,i.miscDemographics_MasterId,GETDATE() FROM inserted i –END WHERE NOT EXISTS (SELECT * FROM dbo.miscDemographics_Relation mrl WHERE i.miscDemographics_ID=mrl.miscDemographics_ID AND ISNULL(i.miscDemographics_MasterId,0)=ISNULL(mrl.miscDemographics_MasterId,0))…

ALTER FUNCTION fn_Select_Age_Range ( @dob datetime ) RETURNS nvarchar(200) AS BEGIN declare @year int declare @month int declare @Days int declare @AgeRange nvarchar(200) SELECT @year=Years, @month=Months,@Days=[Days] FROM dbo.fnDateDifference(@dob,GETDATE()) select @AgeRange= SUBSTRING(dmnValue_Text,1,CHARINDEX (‘-‘,dmnValue_Text)-1) +’ To ‘+ SUBSTRING(dmnValue_Text,CHARINDEX (‘-‘,dmnValue_Text)+1,LEN(dmnValue_Text)-1)+’ Year’ from dmnValue where dmnValue_dmnValueGroup_ID=11 and @year between CAST(SUBSTRING(dmnValue_Text,1,CHARINDEX (‘-‘,dmnValue_Text)-1)as int) and CAST( SUBSTRING(dmnValue_Text,CHARINDEX (‘-‘,dmnValue_Text)+1,LEN(dmnValue_Text)-1) as int) return…

exec sp_help ‘tablename’…