Get List of Tables associated with an SP

Hello Guys,

Often when we go through an SP which has been wriiten by someone else,

we require the table’s used in the SP.

Following script will return the list of tables used in an SP

by taking SP name as parameter.

 

select (select name
        from   sys.schemas
        where  schema_id = t.schema_id)  +’.’+name
      
from   sys.tables as t
where  t.object_id in (select depid
                       from   sys.sysdepends
                       where  id in (select s.object_id
                                     from   sys.procedures as s
                                     where  name like ‘%Employee Sales by Country%’));

 

image

Hope this helps…..

Advertisements

Automative way to Insert Set NoCount On for all the procedures of your Database

SET NOCOUNT ON prevents the sending of DONE_IN_PROC messages to the client for each statement in a stored procedure.

For stored procedures that contain several statements that do not return much actual data, or for procedures that contain

Transact-SQL loops, setting SET NOCOUNT to ON can provide a significant performance boost, because network traffic is greatly reduced.

 

Following procedure put’s Set NoCount On for all the procedures of your Database in SQL Server:

SET NOCOUNT ON

DECLARE
@ProcName NVARCHAR(255)
, @ProcCode NVARCHAR(MAX)
, @Line NVARCHAR(4000)
, @RowNo INT
, @MaxRowNo INT
, @MinRowNo INT
, @Inserted INT
, @Append BIT
, @Rows INT
, @NL CHAR(2)

SELECT
@NL = CHAR(13) + CHAR(10)
, @MinRowNo = 1
, @MaxRowNo = 0

— Create table to hold text from stored procs

SELECT
@ProcCode = ”
IF OBJECT_ID(‘tempdb..#PROC’) IS NOT NULL Begin DROP TABLE #PROC End
IF OBJECT_ID(‘tempdb..#TempForProcsToSetNoCount’) IS NOT NULL Begin DROP TABLE #TempForProcsToSetNoCount End

CREATE TABLE #PROC
(
[RowNo] INT IDENTITY(1, 1)
, [ProcLine] NVARCHAR(4000)
)

— Select all stored procs that do not mention set nocount on

    Select
    routine_schema + ‘.’ + routine_name as ‘ProcName’
    into #TempForProcsToSetNoCount
    from
    information_schema.routines
    where routine_schema = ‘dbo’
    and routine_type = ‘PROCEDURE’
    –and routine_name like @Prefix + ‘%’
    AND LOWER(ROUTINE_DEFINITION) NOT Like  ‘%set nocount on%’
    –Select * from #TempForProcsToSetNoCount

 

    — Insert starting code to replicate SQL MS script behaviour
    Declare @CountForProc int
    Select  @CountForProc = COUNT(1) from #TempForProcsToSetNoCount
    
     While (@CountForProc > 0)
     Begin
      Select Top 1 @ProcName = ProcName from #TempForProcsToSetNoCount
        –Set @ProcName = ‘npspro.USPMRGUpdateNMoveUpFLGFLTValues’
       
        INSERT INTO #PROC
        ( ProcLine )
        VALUES (@NL),
        ( ‘SET ANSI_NULLS ON’ )
        ,( @NL )
        ,( ‘GO’ )
        ,( @NL )
        ,( ‘SET QUOTED_IDENTIFIER ON’ )
        ,( @NL )
        ,( ‘GO’ )
        ,( @NL )
        –END
        –Select * from #PROC

        INSERT #PROC
        EXEC sp_helpText @ProcName
        SELECT
        @ROWS = @@ROWCOUNT
        , @MaxRowNo = MAX(RowNo)
        FROM
        #PROC
        –Select @MaxRowNo
        –Select @MinRowNo,@NL
        –Select * from #PROC
        –Where @MinRowNo<>@MaxRowNo

    –Find line in proc that contains the AS keyword after parameter declaration
   
    SELECT
    @RowNo = RowNo
    FROM
    #PROC
    WHERE
    RowNo BETWEEN @MinRowNo AND @MaxRowNo — work with current proc only
    AND ( ProcLine LIKE ‘AS[^A-Z]%’
    OR RIGHT(ProcLine, 5) = ‘ AS’ + @NL )
    –Select @RowNo
   
   
    IF(@RowNo is null)
   
    Select ‘pass’
    Select TOP 1 @RowNo= RowNo
    FROM
    #PROC
    WHERE
    RowNo BETWEEN @MinRowNo AND @MaxRowNo — work with current proc only
    AND  ProcLine LIKE ‘%AS[^A-Z]%’

    –Select     @MinRowNo,@MaxRowNo,@RowNo

    –Update row with SET NOCOUNT ON
    UPDATE #PROC
    SET ProcLine =  ProcLine + @NL + ‘SET NOCOUNT ON’ + @NL
    WHERE RowNo = @RowNo
    — Change CREATE to ALTER
    UPDATE
    #PROC
    SET ProcLine =
     Case When ProcLine like ‘%CREATE PROC%’ Then REPLACE(ProcLine, ‘CREATE PROC’,
    ‘ALTER PROC’)
    Else REPLACE(ProcLine, ‘%CREATE PROCEDURE%’,
    ‘ALTER PROC’)
    End
    WHERE
    RowNo BETWEEN @MinRowNo AND @MaxRowNo

    — Set marker for next proc
    SELECT
    @MinRowNo = @MaxRowNo
   
   
    Set @CountForProc = @CountForProc – 1
    –Set @CountForProc = -1
    Delete from #TempForProcsToSetNoCount where ProcName = @ProcName
   
    END
–CLOSE PROCS
–DEALLOCATE PROCS

— Workaround for limitations displaying long strings from variables in query analyser
— and to get round the fact that sp_helptext will split lines up when it shouldn’t
DECLARE @RESULTS TABLE(RowNo int Identity(1,1),ProcLine nvarchar(4000))

SELECT      @RowNo = 1
          , @Inserted = 0
          , @Append = 0

WHILE @RowNo < @MaxRowNo
  BEGIN
      SELECT      @Line = ProcLine
      FROM  #PROC
      WHERE RowNo = @RowNo

      — If last line did not end in new line we append this line to the end of it
      IF @Append = 1
        BEGIN
            UPDATE      @RESULTS
            SET         ProcLine = ProcLine + @Line
            WHERE RowNo = @Inserted — add to last line we inserted
        END
      ELSE       
        BEGIN
            INSERT INTO @RESULTS
            SELECT @Line
            — increment our inserted rows counter
            SELECT @Inserted = @Inserted + 1                     
        END
     
      –Check whether the end of the current line ends in a carriage return line feed
      –if it does we know to append the next line to the last inserted one
      IF RIGHT(@Line,2) <> @NL
        SELECT @Append = 1 — flag for update        
      ELSE
      SELECT @Append = 0 — make sure we insert a new line
      SELECT @RowNo = @RowNo + 1
  END

— Output all code in order as each line is under 4000 chars we can view this in QA
SELECT      ProcLine
FROM  @RESULTS
ORDER BY RowNo

 

After running the above script on your database by providing a proper schema in the script and

running it on your respective database,you can execute the output of above script by putting it

in another query so that changes are reflected in your SP’s.

Hope this method save’s your precious time and also manual work.

RANGE VS ROW in SQL Server 2012

Understanding the difference

ROWS is managed by the physical position of the row, independent whether the values of the columns specified in the
order by clause repeat or not.

If RANGE is specified and the data in the rows duplicate it is considered as ONE range, like a distinct of the rows.

Example:

USE tempdb
GO
IF OBJECT_ID(‘tempdb.dbo.#TMP’)  IS NOT NULL
  DROP TABLE #TMP
GO
CREATE TABLE #TMP (ID Int, Col1 Char(1), Col2 Int)
GO

INSERT INTO #TMP VALUES(1,’A’, 5), (2, ‘A’, 5), (3, ‘B’, 5), (4, ‘C’, 5), (5, ‘D’, 5)
GO
SELECT * FROM #TMP

 

SELECT *,
       SUM(Col2) OVER(ORDER BY Col1 RANGE UNBOUNDED PRECEDING) “Range”,
       SUM(Col2) OVER(ORDER BY Col1 ROWS UNBOUNDED PRECEDING) “Rows”
  FROM #TMP

SQL Server 2012: What’s new in T-SQL?

  • Sequence object

Sequence object is similar to identity property of a column with only difference being we can use its value across multiple table’s.We can also create cyclic sequence object and play a lot with this new feature of SQL Server 2012.

  • PAGING windows in TSQL

In SQL Server 2008 R2,there was not much built in functionality available for developers for paging especially required by UI developers.But SQL Server 2012 has made available this functionality by providing Offset Fetch next keywords which can be used after Order by clause.

  • Exception Handling in SQL Server 2012

    In SQL Server 2008 R2 it was a best practice to implement exception handling using a TRY/CATCH block. SQL Server 2012 allows you to implement a THROW exception that will be used in conjunction with the TRY/CATCH block and THROW the exception as it was caught by the CATCH block.

  • New Functions in SQL Server 2012

Some of the new functions introduced in SQL Server 2012:

1. String Manipulation using CONCAT:

CONCAT function concatenates 2 or more values together into a single string   expression. NULL values by default will not impact the result set and will be considered as empty values when using in combination with the CONCAT function      

           Example, SELECT CONCAT ( ‘DBA’, ‘Mentalist’ ) AS Result;

     

2. FORMAT function:

Specified format and optional culture or language setting.The FORMAT function returns a value formatted with a FORMAT ( value, format [, culture ] )

DECLARE @d DATETIME = GETDATE();

SELECT FORMAT( @d, ‘dd/MM/yyyy’, ‘en-US’ ) AS Result;

3.Logical functions IIF and CHOOSE

The IIF statement is a shorter way to write a case statement in which only one or another value will be returned base on that the Boolean expression defined.

DECLARE @a int = 10;

DECLARE @b int = 20;

SELECT IIF ( @a < @b, ‘TRUE’, ‘FALSE’ ) AS Result;

The CHOOSE statement returns an item, specified based on the index from a list of values.

SELECT CHOOSE ( 3, ‘PM’, ‘TL’, ‘Developer’, ‘Tester’ ) AS Result;

4.TRY_CONVERT function in SQL Server 2012

TRY_CONVERT takes the value passed to it and tries to convert it to the specified data_type. If the cast succeeds, TRY_CONVERT returns the value as the specified data_type; if an error occurs, null is returned. However if you request a conversion that is explicitly not permitted, then TRY_CONVERT fails with an error.