Collection of frequently used SQL

-

I have gathered some SQL queries and commands frequently used by developers (like me ☺) to reduce the googling time.

For quick reference query titles are enlisted below with links:

Commonly Used Queries

Get all columns with specific name

This query could be useful when we've got a column name, but don't know which table it belongs to. 
-- Find All Columns From All Tables With Similar Name
SELECT t.name TableName, c.name ColumnName
FROM sys.columns c
JOIN sys.tables t ON c.object_id = t.object_id
WHERE c.name LIKE '%column_name%'

Get all connected tables

This query is particularly useful when changing the definition of a table or deleting records from a table. Records in connected table(s) can prevent deleting master records or dropping the table. 
-- Get All Connected Tables
SELECT f.name AS ForeignKeyName, p.name AS ParentTable, rc.name AS ParentColumn, c.name AS ForeignTable, fc.name AS ForeignColumn
FROM sysobjects f
JOIN sysobjects c ON f.parent_obj = c.id
JOIN sysreferences r ON f.id = r.constid
JOIN sysobjects p ON r.rkeyid = p.id
JOIN syscolumns rc ON r.rkeyid = rc.id AND r.rkey1 = rc.colid
JOIN syscolumns fc ON r.fkeyid = fc.id AND r.fkey1 = fc.colid
WHERE f.type = 'F' 
AND (p.name = 'table_name' OR c.name = 'table_name')

Get all foreign key references of a specific table

This is a quick one and the most frequent one for me. It is useful for checking child tables. 
-- Get All Foreign Key References Of Table
EXEC sp_fkeys 'table_name'

Get all foreign key references of a specific column of a table

-- Get All Foreign Key References Of Specific Column
SELECT f.name AS ForeignKeyName, c.name AS ForeignKeyTable, fc.name AS ForeignKeyColumn
FROM sysobjects f
JOIN sysobjects c ON f.parent_obj = c.id
JOIN sysreferences r ON f.id = r.constid
JOIN sysobjects p ON r.rkeyid = p.id
JOIN syscolumns rc ON r.rkeyid = rc.id AND r.rkey1 = rc.colid
JOIN syscolumns fc ON r.fkeyid = fc.id AND r.fkey1 = fc.colid
WHERE p.name = 'table_name' AND rc.Name = 'column_name'

Search across all tables for specific value

This query is to search for a specific value across all tables in the current database. 
DECLARE @SearchStr NVARCHAR(200) = 'With fresh paint'
DECLARE @ProbableDataType NVARCHAR(100) = 'varchar'
IF(@SearchStr IS NULL OR @SearchStr = '')
    SELECT 'Search string is not valid.'
ELSE
BEGIN
 IF(@ProbableDataType IS NOT NULL AND NOT EXISTS(SELECT * FROM SYS.Types WHERE Name = @ProbableDataType))
SELECT 'Type is not valid.'
ELSE 
 BEGIN
  SET NOCOUNT ON
  DECLARE @DataTypes TABLE(TypeName NVARCHAR(100))  
  IF @ProbableDataType IS NULL OR @ProbableDataType = ''
      INSERT INTO @DataTypes 
      SELECT value FROM (VALUES  ('varchar'),('nvarchar'),('text'),('ntext'),('int'),('decimal'),('xml'),('datetime')) AS tbl(value)
  ELSE IF @ProbableDataType = 'varchar' OR @ProbableDataType = 'nvarchar'
      INSERT INTO @DataTypes 
      SELECT value FROM (VALUES  ('varchar'),('nvarchar'),('text'),('ntext')) AS tbl(value)
  ELSE IF @ProbableDataType = 'numeric' OR @ProbableDataType = 'int' OR @ProbableDataType = 'bigint' OR @ProbableDataType = 'float' OR @ProbableDataType = 'decimal'
      INSERT INTO @DataTypes 
      SELECT value FROM (VALUES  ('numeric'),('int'),('bigint'),('decimal'),('float')) AS tbl(value)
  ELSE IF @ProbableDataType = 'datetime' OR @ProbableDataType = 'smalldatetime'
      INSERT INTO @DataTypes 
      SELECT value FROM (VALUES  ('datetime'),('smalldatetime'),('datetime2'),('date')) AS tbl(value)
  ELSE
      INSERT INTO @DataTypes VALUES(@ProbableDataType) 
  
  SELECT (QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)) AS [Table], 0 AS [IsSearched]
  INTO #SearchTables 
  FROM INFORMATION_SCHEMA.TABLES 
  WHERE TABLE_TYPE = 'BASE TABLE'   
  AND OBJECTPROPERTY(OBJECT_ID(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)), 'IsMSShipped') = 0
  DECLARE @SearchResults TABLE (TableName NVARCHAR(128), ColumnName NVARCHAR(128), ColumnValue NVARCHAR(2000))  
  DECLARE @TableName NVARCHAR(128)
  WHILE(EXISTS(SELECT * FROM #SearchTables WHERE IsSearched = 0))
  BEGIN
   SET @TableName = (SELECT TOP 1 [Table] FROM #SearchTables WHERE IsSearched = 0)   
      
SELECT (QUOTENAME(COLUMN_NAME)) AS [Column], 0 AS IsSearched 
   INTO #SearchColumns
FROM INFORMATION_SCHEMA.COLUMNS
            WHERE TABLE_SCHEMA = PARSENAME(@TableName, 2)
            AND TABLE_NAME = PARSENAME(@TableName, 1)
            AND (DATA_TYPE IN (SELECT TypeName FROM @DataTypes))
   DECLARE @ColumnName NVARCHAR(128)            
   WHILE(EXISTS(SELECT * FROM #SearchColumns WHERE IsSearched = 0))
   BEGIN
    SET @ColumnName = (SELECT TOP 1 [Column] FROM #SearchColumns WHERE IsSearched = 0)    
INSERT INTO @SearchResults
    EXEC
    (
     'SELECT ''' + @TableName + ''', ''' + @ColumnName + ''', SUBSTRING(' + @ColumnName + ', 1, 2000) FROM ' + @TableName + ' (NOLOCK) ' +
     ' WHERE ' + @ColumnName + ' LIKE ''%' + @SearchStr + '%'''
    )
    UPDATE #SearchColumns
    SET IsSearched = 1
    WHERE [Column] = @ColumnName
   END
   DROP TABLE #SearchColumns
   UPDATE #SearchTables
   SET IsSearched = 1
   WHERE [Table] = @TableName
  END
  --SELECT [Table] FROM #SearchTables
SELECT * FROM @SearchResults
 DROP TABLE #SearchTables
 END
END

Some Useful Syntax

Get data with pagination

DECLARE @PageSize INT, @CurrentPage INT;
SET @PageSize = 10
SET @CurrentPage = 3
 
SELECT * FROM 
(
 SELECT Count(1) OVER () AS TotalCount,
        ROW_NUMBER() OVER(ORDER BY BusinessEntityId) AS RowNumber, 
        *
 FROM [Person].[Person]
 -- add additional filters
) AllRecords 
WHERE RowNumber > @PageSize * (@CurrentPage-1) AND RowNumber <= @PageSize * @CurrentPage

A little more thought about  pagination can be found here.

Rotate data (PIVOT and UNPIVOT)

-- Pivot Table To Transform Rows (records) Into Column
SELECT * FROM
(
    SELECT UnitPrice * OrderQty AS Sales, DATENAME(MONTH, OrderDate) AS [Month]
    FROM Sales.SalesOrderHeader o 
    JOIN Sales.SalesOrderDetail i ON o.SalesOrderID = i.SalesOrderID
    WHERE YEAR(OrderDate) = 2006
) s
PIVOT
(
 SUM(Sales) FOR [Month] IN ([January],[February],[March],[April],[May],[June],[July],[August],[September],[October],[November],[December])
) AS pv

-- Unpivot Data To Transform Column Names into Rows (Records)
SELECT ProductCode, Attribute, Value  
FROM Computer
UNPIVOT
(
 Value
 FOR Attribute IN ([Category], [Brand], [Model], [Color])
) u

Using common table expression (CTE)

-- Recursive Common Table Expression
;WITH Employee_CTE (EmployeeId, FirstName, LastName, ManagerId, LevelOnHierarchy)
AS
(
 SELECT EmployeeId, FirstName, LastName, ManagerId, 1
 FROM Employee
 WHERE ManagerId IS NULL
 UNION ALL
 SELECT e.EmployeeId, e.FirstName, e.LastName, e.ManagerId, c.LevelOnHierarchy + 1
 FROM Employee e
 JOIN Employee_CTE c ON e.ManagerId = c.EmployeeId
)
SELECT FirstName, LastName, LevelOnHierarchy
FROM Employee_CTE

Ranking data

-- Ranking For Top 10 Sales Persons
-- Partition is optional
-- If values have a tie, raking will be same while "skipping" next ranks
SELECT TOP 10
 p.FirstName, 
 p.LastName,
 s.TerritoryID,
 RANK() OVER (PARTITION BY s.TerritoryID ORDER BY SalesYTD DESC) AS [Rank],   
 s.SalesYTD
FROM Sales.SalesPerson s 
JOIN Person.Person p ON s.BusinessEntityID = p.BusinessEntityID
JOIN Person.Address a ON a.AddressID = p.BusinessEntityID
WHERE s.TerritoryID IS NOT NULL

-- Consequtive Ranking For Top 10 Sales Persons
-- Partition is optional
SELECT TOP 10
 p.FirstName, 
 p.LastName,
 s.TerritoryID,
        DENSE_RANK() OVER (PARTITION BY s.TerritoryID ORDER BY SalesYTD DESC) AS [Rank],   
 s.SalesYTD
FROM Sales.SalesPerson s 
JOIN Person.Person p ON s.BusinessEntityID = p.BusinessEntityID
JOIN Person.Address a ON a.AddressID = p.BusinessEntityID
WHERE s.TerritoryID IS NOT NULL

-- Grouping For Top 20 Sales Persons
-- Partition is optional
-- Divides rows into five groups of sales persons based on their sales value
SELECT TOP 20
 p.FirstName, 
 p.LastName,
        NTILE(5) OVER (PARTITION BY s.TerritoryID ORDER BY SalesYTD DESC) AS [Rank],   
 s.SalesYTD
FROM Sales.SalesPerson s 
JOIN Person.Person p ON s.BusinessEntityID = p.BusinessEntityID
JOIN Person.Address a ON a.AddressID = p.BusinessEntityID

Get values from XML column

-- With expression is not required if namespace not specified at root node
;WITH XMLNAMESPACES(DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/StoreSurvey')
SELECT 
Demographics.value('(/StoreSurvey/BankName)[1]', 'nvarchar(max)') AS BankName,
Demographics.value('(/StoreSurvey/NumberEmployees)[1]', 'nvarchar(max)') AS NumberOfEmployees
FROM Sales.Store

Some Useful Commands

Get database server information

-- Get Database Server Information
SELECT  SERVERPROPERTY('MachineName') AS MachineName,
        CASE WHEN SERVERPROPERTY('InstanceName') IS NULL THEN 'Default' ELSE SERVERPROPERTY('InstanceName') END AS Instance,
        SERVERPROPERTY('Edition') AS Edition,
        SERVERPROPERTY('ProductLevel') AS ProductLevel,
        CASE SERVERPROPERTY('IsClustered') WHEN 1 THEN 'CLUSTERED' ELSE 'STANDALONE' END AS ServerType,
        @@VERSION AS VersionNumber

Get all database names

-- Get All Database Names
SELECT db_name(database_id) AS DatabaseName,  
        type_desc AS DataType,
        physical_name AS PhysicalPath
FROM sys.master_files

Comments

Post a Comment

Popular posts from this blog

Adding security headers to prevent XSS and MiTM attacks in .Net Core

-
Image
As a developer, we need to consider security when designing and building web applications. HTTP Response Headers allow server to pass additional information to instruct browsers how to handle sensitive data and content of the application and/or from external or untrusted sources. HTTP response security headers provide an extra layer of protection to help mitigating vulnerabilities and attacks. One way to add those security headers from .Net Core application is by writing a custom action filter that would be executed before serving any response from the application. Below is a example of the custom attribute: To apply this globally for all responses, we can add this to the Startup class as in below: To apply trusted source for any particular action: And here is the outcome: HAPPY CODING   👌
Read more

Creating transformations for custom config files

-
Image
Visual Studio provides native support to add application config transforms. But it is restricted to the native configuration files only. Also by default it only provides transforms for debug and release environment. If you need to use your own custom configuration file for custom defined environment, below are two options you can follow: Option 1: Do it "manually" Let's assume, we have a configuration file: Configuration.xml in a project. We want to add Configuration.Staging.xml and Configuration.Production.xml as transforms. To achieve this, I can add those xml files manually first and then edit the project file (.csproj) as below and then re-load the project: <ItemGroup>     <Content Include =" Configuration.xml " >        <SubType> Designer </SubType>        <TransformOnBuild> true </TransformOnBuild>        ...
Read more

Microsoft.IdentityModel.Protocols.OpenIdConnectProtocolInvalidNonceException

-
I was using openId connect authentication with IdentityServer (v4) in a recent project and my client application was throwing following error: Microsoft.IdentityModel.Protocols.OpenIdConnectProtocolInvalidNonceException: IDX10311: RequireNonce is 'true' (default) but validationContext.Nonce is null. A nonce cannot be validated. If you don't need to check the nonce, set OpenIdConnectProtocolValidator.RequireNonce to 'false'. at Microsoft.IdentityModel.Protocols.OpenIdConnectProtocolValidator.ValidateNonce(JwtSecurityToken jwt, OpenIdConnectProtocolValidationContext validationContext) in c:\workspace\WilsonForDotNet45Release\src\Microsoft.IdentityModel.Protocol.Extensions\OpenIdConnectProtocolValidator.cs:line 332 at Microsoft.IdentityModel.Protocols.OpenIdConnectProtocolValidator.Validate(JwtSecurityToken jwt, OpenIdConnectProtocolValidationContext validationContext) in c:\workspace\WilsonForDotNet45Release\src\Microsoft.IdentityModel.Protocol.Extensions\OpenIdCo...
Read more