Top 8 Useful and Important Scripts in SQL - Part 2

Next Post
By Admin at 16 Mar 2010, 17:54 PM
  • 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".

    1. SELECT * FROM INFORMATION_SCHEMA.COLUMNS
    2. 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.

    1. 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

    1. DECLARE @Dates TABLE(DateCol DATETIME)
    2. DECLARE @StartDate DATETIME
    3. DECLARE @EndDate DATETIME
    4. SET @StartDate = '20051228'
    5. SET @EndDate = '20060106'
    6. WHILE @StartDate<=@EndDate
    7. BEGIN
    8. INSERTINTO @Dates VALUES (@StartDate)
    9. SET @StartDate=@StartDate+1
    10. END
    11. SELECT DateCol, DATENAME(WEEKDAY,DATEADD(DAY,0,DateCol)) AS DayName
    12. 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

    1. WITH DateList AS
    2. (
    3. SELECTCAST('2000-10-01'AS DATETIME) DateValue
    4. UNIONALL
    5. SELECT DateValue + 1
    6. FROM DateList
    7. WHERE DateValue + 1 < '2000-12-31'
    8. )
    9. SELECT DateValue
    10. 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.

    1. SET ANSI_NULLS ON
    2. GO
    3. SET QUOTED_IDENTIFIER ON
    4. CREATETABLE [dbo].[Child]
    5. (
    6. [intID_pk] [int] IDENTITY(1,1) NOTNULL,
    7. [intParentID_fk] [int] NULL,
    8. [chvFirstName] [varchar](100) NULL,
    9. [chvLastName] [varchar](100) NULL,
    10. CONSTRAINT [PK_Child] PRIMARYKEY CLUSTERED
    11. (
    12. [intID_pk] ASC
    13. )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
    14. ) ON [PRIMARY]
    15. GO
    16. INSERTINTO Child(intParentID_fk,chvFirstName,chvLastName)
    17. VALUES(1,'Suresh','G')
    18. INSERTINTO Child(intParentID_fk,chvFirstName,chvLastName)
    19. VALUES(1,'Ramesh','k')
    20. INSERTINTO Child(intParentID_fk,chvFirstName,chvLastName)
    21. VALUES(1,'Ganesh','M')
    22. INSERTINTO Child(intParentID_fk,chvFirstName,chvLastName)
    23. VALUES(2,'Gayu','K')
    24. INSERTINTO Child(intParentID_fk,chvFirstName,chvLastName)
    25. VALUES(2,'Karthik','J')
    26. GO
    27. CREATEFUNCTION fn_Get_Childs(@intParentID_fk INT)
    28. RETURNSVARCHAR(MAX)
    29. AS
    30. BEGIN
    31. DECLARE @ValueList VARCHAR(100)
    32. SELECT @ValueList = COALESCE(@ValueList + ', ', '') +
    33. CAST(chvFirstName+ ' '+chvLastName ASVARCHAR(200))
    34. FROM Child WHERE intParentID_fk=@intParentID_fk
    35. RETURN @ValueList
    36. END
    37. GO
    38. SELECT dbo.fn_Get_Childs(1)

    Output:

    Suresh G, Ramesh k, Ganesh M


    Comments

     

    Post a comment

    Please correct the following: