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

The credentials supplied to the package were not recognized

-
I am using ravendb as database in my current project and got tired of the following exception when running the database and the web site on the same machine: The credentials supplied to the package were not recognized Description:   An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.   Exception Details:   System.ComponentModel.Win32Exception: The credentials supplied to the package were not recognized [Win32Exception (0x80004005): The credentials supplied to the package were not recognized] System.Net.NTAuthentication.GetOutgoingBlob(Byte[] incomingBlob, Boolean throwOnError, SecurityStatus& statusCode) +7737075 System.Net.NTAuthentication.GetOutgoingBlob(String incomingBlob) +91 System.Net.NegotiateClient.DoAuthenticate(String challenge, WebRequest webRequest, ICredentials credentials, Boolean preAuthenticate...
Read more

Executing synchronous methods asynchronously

-
We can achieve certain advantages by writing asynchronous methods. It's pretty simple to write them in C# code: we just need to use  async  and  await keywords properly. Grammar of these keywords can be found in detail in MSDN . In this post I will try to focus on writing few code to invoke a regular synchronous method asynchronously. Let's assume a scenario where we need to perform an expensive database operation when a particular information is changed from the front end. For example: I have an application to manage employee information and I've changed the basic salary for a particular grade using my application. Now I need to update salary of all the employees under that grade. The later operation will be slower one and I don't want this to block me from doing other stuffs. Here is a piece of synchronous code to update salary information: public   bool   UpdateEmployeeSalary ( string   grade ) {      try  ...
Read more