Top 8 Useful and Important Scripts in SQL - Part 2 - SQL Programmers

5. Search table names or column names in SQL.

The following scripts result in a list of table and column names which contain the word "Product".

    SELECT * FROM INFORMATION_SCHEMA.COLUMNS  
    WHERE Table_Name like '%Product%' OR column_name like '%Product%'

 6. List tables of a SQL database

The following script provides a list of all table names in the database.

SELECT '['+SCHEMA_NAME(schema_id)+'].['+name+']' AS SchemaTable FROM sys.tables

7. SQL select dates between two determined dates

This script is used to select dates between two given dates. If you want records with dates in between 12/28/2005 and 01/06/2006, the script below helps to display the results.

With While Loop

DECLARE @Dates TABLE(DateCol DATETIME)  
    DECLARE @StartDate DATETIME  
    DECLARE @EndDate DATETIME  
      
    SET @StartDate = '20051228'  
    SET @EndDate = '20060106'  
      
    WHILE @StartDate<=@EndDate  
    BEGIN  
        INSERT INTO @Dates VALUES (@StartDate)  
        SET @StartDate=@StartDate+1  
    END  
         
    SELECT DateCol, DATENAME(WEEKDAY,DATEADD(DAY,0,DateCol)) AS DayName  
    FROM @Dates  
     

 Output:
DateCol     DayName
2005-12-29 00:00:00.000     Thursday
2005-12-30 00:00:00.000     Friday
2005-12-31 00:00:00.000     Saturday
2006-01-01 00:00:00.000     Sunday
2006-01-02 00:00:00.000     Monday
2006-01-03 00:00:00.000     Tuesday
2006-01-04 00:00:00.000     Wednesday
2006-01-05 00:00:00.000     Thursday
2006-01-06 00:00:00.000     Friday

Note: If you use these above query for more records then deadlock issue will raise

Without Using While Loop

WITH DateList AS  
    (  
    SELECT CAST('2000-10-01' AS DATETIME) DateValue  
    UNION ALL  
    SELECT DateValue + 1  
    FROM    DateList     
    WHERE   DateValue + 1 < '2000-12-31'  
    )  
    SELECT DateValue  
    FROM DateList

8. Select Multiple Rows in One Row

This script is used to select and display the data of multiple rows in one single row. If you select the child name for a particular parent id, all child names are displayed in single row.

    SET ANSI_NULLS ON  
    GO  
    SET QUOTED_IDENTIFIER ON  
    CREATE TABLE [dbo].[Child]  
    (  
        [intID_pk] [int] IDENTITY(1,1) NOT NULL,  
        [intParentID_fk] [int] NULL,  
        [chvFirstName] [varchar](100)   NULL,  
        [chvLastName] [varchar](100)  NULL,  
     CONSTRAINT [PK_Child] PRIMARY KEY CLUSTERED  
    (  
        [intID_pk] ASC  
    )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]  
    ) ON [PRIMARY]  
      
    GO  
    INSERT INTO Child(intParentID_fk,chvFirstName,chvLastName)  
    VALUES(1,'Suresh','G')  
    INSERT INTO Child(intParentID_fk,chvFirstName,chvLastName)  
    VALUES(1,'Ramesh','k')  
    INSERT INTO Child(intParentID_fk,chvFirstName,chvLastName)  
    VALUES(1,'Ganesh','M')  
    INSERT INTO Child(intParentID_fk,chvFirstName,chvLastName)  
    VALUES(2,'Gayu','K')  
    INSERT INTO Child(intParentID_fk,chvFirstName,chvLastName)  
    VALUES(2,'Karthik','J')  
    GO  
    CREATE FUNCTION fn_Get_Childs(@intParentID_fk INT)  
    RETURNS VARCHAR(MAX)  
    AS  
    BEGIN  
    DECLARE @ValueList VARCHAR(100)  
    SELECT @ValueList = COALESCE(@ValueList + ', ', '') +  
       CAST(chvFirstName+ ' '+chvLastName AS VARCHAR(200))  
    FROM Child WHERE intParentID_fk=@intParentID_fk  
    RETURN @ValueList  
    END  
    GO  
    SELECT dbo.fn_Get_Childs(1)

Output:

Suresh G, Ramesh k, Ganesh M