Get list of all tables created by you.

SELECT * FROM sys.objects WHERE type = 'u' ORDER BY [name]code-box


How to check if a table has a primary key column or not?

A table in any relational database must have a primary key in it. It will improve the performance of your queries and will not lead to deadlock issues if data is huge and you have to run UPDATE queries on it.

Below is the script you can run to check the presence of a primary key.

SELECT * FROM information_schema.table_constraints WHERE constraint_type = 'PRIMARY KEY' AND table_name = 'categories'code-box


Remove Square Brackets from JSON

SELECT 2021 as yr, 1 as mon, 15 as dd FOR JSON PATH, WITHOUT_ARRAY_WRAPPERcode-box


Select from another result set or a query and perform inner join

You can further narrow down or filter results of a query by adding another select query on its top.
Like I have used it here after applying grouping on child table and then joining it with parent table to get the desired results. select sub.userid, sub.sitecount, s.officename,s.username from( select userid,count(*) sitecount from ara_stbookingdetail group by userid ) as sub inner join Ara_STUser s on s.userid = sub.useridcode-box


Generate Model Class from Database Table - SQL Server

declare @TableName sysname = 'TableName' declare @Result varchar(max) = 'public class ' + @TableName + ' {' select @Result = @Result + ' public ' + ColumnType + NullableSign + ' ' + ColumnName + ' { get; set; } ' from ( select replace(col.name, ' ', '_') ColumnName, column_id ColumnId, case typ.name when 'bigint' then 'long' when 'binary' then 'byte[]' when 'bit' then 'bool' when 'char' then 'string' when 'date' then 'DateTime' when 'datetime' then 'DateTime' when 'datetime2' then 'DateTime' when 'datetimeoffset' then 'DateTimeOffset' when 'decimal' then 'decimal' when 'float' then 'double' when 'image' then 'byte[]' when 'int' then 'int' when 'money' then 'decimal' when 'nchar' then 'string' when 'ntext' then 'string' when 'numeric' then 'decimal' when 'nvarchar' then 'string' when 'real' then 'float' when 'smalldatetime' then 'DateTime' when 'smallint' then 'short' when 'smallmoney' then 'decimal' when 'text' then 'string' when 'time' then 'TimeSpan' when 'timestamp' then 'long' when 'tinyint' then 'byte' when 'uniqueidentifier' then 'Guid' when 'varbinary' then 'byte[]' when 'varchar' then 'string' else 'UNKNOWN_' + typ.name end ColumnType, case when col.is_nullable = 1 and typ.name in ('bigint', 'bit', 'date', 'datetime', 'datetime2', 'datetimeoffset', 'decimal', 'float', 'int', 'money', 'numeric', 'real', 'smalldatetime', 'smallint', 'smallmoney', 'time', 'tinyint', 'uniqueidentifier') then '?' else '' end NullableSign from sys.columns col join sys.types typ on col.system_type_id = typ.system_type_id AND col.user_type_id = typ.user_type_id where object_id = object_id(@TableName) ) t order by ColumnId set @Result = @Result + ' }' print @Result code-box


Begin transaction for running update, insert or delete queries

BEGIN TRAN T1; -- Query COMMIT TRAN T1; -- OR ROLLBACK TRAN T1;code-box


Inner Join Child table and get Max value record

SELECT CU.CustomerUnitID, CU.FileNo, C.FirstName, C.LastName, C.Address1, (C.Mobile+' ,'+C.Phone1) AS Phone, dbo.GetUnitType(CU.UnitID) as UnitType, CaU.Reason, CaU.CancelOn AS AddedOn FROM Ara_CustomerUnit CU JOIN (SELECT CustomerUnitID, Reason, CancelOn, ROW_NUMBER() OVER(PARTITION BY CustomerUnitID ORDER BY RecordId DESC) AS RowNumber FROM [Ara_CancelledUnit]) AS CaU ON CU.CustomerUnitID = CaU.CustomerUnitID INNER JOIN Ara_Unit U ON CU.UnitId = U.UnitId INNER JOIN Ara_Customer C ON CU.CustomerID=C.CustomerID INNER JOIN Ara_Project P ON U.ProjectID=P.ProjectID WHERE CU.Status=@FileStatus AND CaU.RowNumber=1 AND P.ProjectId=@ProjectID AND (@OfficeID IS NULL OR CU.AddedBy IN(SELECT UserID FROM ARA_USER WHERE OfficeID=@OfficeID)) ORDER By CaU.AddedOn DESC code-box


Add CASE in WHERE or Compare Parameter in WHERE clause

ALTER PROC UserHasAccess @TenantId INT, @PortalId INT, @URL VARCHAR(256), @HasAccess BIT OUTPUT AS IF EXISTS(SELECT TP.PageId FROM TenantPage TP INNER JOIN QCSections AP ON TP.PageId = AP.Id WHERE --AP.PortalId=@PortalId AND TP.TenantId=@TenantId AND @URL = CASE CHARINDEX('?', AP.[URL],0) WHEN 0 THEN [URL] ELSE SUBSTRING([URL],0,CHARINDEX('?',[URL],0)) END ) SET @HasAccess=1 ELSE SET @HasAccess=0 RETURN; code-box


Add Column with default values:

ALTER TABLE table_name ADD column_name data_type NULL CONSTRAINT constraint_name DEFAULT default_value WITH VALUES; code-box


Update table using Inner Join

update j set j.Customer_TimeZone = t.Customer_TimeZone from jobs j inner join Temp_TimeZoneJobs t on j.jobid = t.jobid code-box


Get only time

SELECT * FROM Ara_Payment WHERE CONVERT(VARCHAR,adddate,8) = '00:00:00' ORDER BY AddDate code-box

Post a Comment

Previous Post Next Post