SQL to get the names of the tables referenced in stored procedures

below is a simple sql to retrieve a list of all the stored procedures and the tables referenced in those procedures:

SELECT DISTINCT a.name AS [Procedure Name], b.name AS [Table Name]
FROM sysobjects as a
INNER JOIN sysdepends d ON a.id=d.id
INNER JOIN sysobjects b ON d.depid=b.id
WHERE a.xtype = 'P'
ORDER BY a.name, b.name

Optimizing stored procedures

1) Add SET NONCOUNT OFF - to prevent your stored procedure from returning number of rows affected, thus reducing network traffic

2) Use fully qualified object names and table names - preceeding the object names with schema name reduces the time it takes to search all schemas for the object, thus reducing the time it takes for a stored proceedure to runMake sure object names and table names

3) Avoid SQL Server searching master database for your stored proceedure by ensuring that none of the proceedure names are preceeded with "sp_", use "usp_" instead, to specify that it's a user stored procedure, not a system one.

4) If proceedure returns an integer value, use RETURN statement to return a single integer value as opposed to returning the value as a part of a recordset.

The RETURN statement exits unconditionally from a stored procedure, so the statements following RETURN are not executed. Though the RETURN statement is generally used for error checking, you can use this statement to return an integer value for any other reason. Using the RETURN statement can boost performance because SQL Server will not create a recordset.

5) Replace all EXECUTE statements with sp_executesql. sp_executesql makes your code more reusable because it takes parameters and eliminates the possibility for sql injection.

The execution plan of a dynamic statement can be reused only if each and every character, including case, space, comments and parameter, is same for two statements. For example, if we execute a sql batch with a parameter, and then execute the same batch with a different value for the parameter, the execution plan created the first time around will be reused for the different value of the parameter. The reuse of the existing complied plan will result in improved performance.

6) When checking for an existance of a particular record, Use IF EXISTS (SELECT 1) instead of (SELECT *) since it minimizes data processing

7) Add indexes to the fields most often used in WHERE clauses and for JOINS. Indexing the right fields can help to significantly improve the performance.

8) Utilize TRY-CATCh blocks, it is much more efficient than the old way of error checking after each sql statement.

BEGIN TRY
--Your t-sql code goes here
END TRY
BEGIN CATCH
--Your error handling code goes here
END CATCH

9) Utilize temp tables. For instance if the same query runs multiple times, run it only once, dumping the results into a temp table.

10) use EXISTS instead of JOINS. If the table you are joining with is not contributing any columns to your query (the select list), then you are using unnecessary IO resources on the background.

11) Avoid using DISTINCT. Usage of DISTINCT may mean you have a bad table design somewhere

12) Limit the SELECT list by returning only the columns you need, since returning too many columns can have a drastic effect on your query. Not only will it increase you chances for bookmark lookups (or key lookups), but the network and disk latency add to the query. Not to mention you will be squeezing more data into your buffer cache.

13)Use the least amount of tables to Compile Your SELECT list. An example would be let’s say you need to join on 2 tables in order to get your result set. If one of the tables contains all the fields needed for the SELECT list, but you are also able to get the same field(s) from the other table, always go with only returning the values from the one table. Doing so will limit the number of IO operations necessary to give you your result.

14)Index temp tables Temp tables are treated just like permanent tables according to SQL. They can have indexes & statistics. The only downfall is that they often cause recompiles for the statement when the result sets differ. To counter this read reducing temp table recompiles or use table variables if you have to.

15) Break down large stored procedures into several sub-procedures and call them from controlling stored procedure.

The stored procedure will be recompiled when any structural changes are made to a table or view referenced by the stored procedure (an ALTER TABLE statement, for example), or when a large number of INSERTS, UPDATES or DELETES are made to a table referenced by a stored procedure. So, if you break down a very large stored procedure into several sub-procedures, there's a chance that only a single sub-procedure will be recompiled, while other sub-procedures will not.

16) Try to avoid using temporary tables inside your stored procedures.

Using temporary tables inside stored procedures reduce the chance to reuse the execution plan.

17) Try to avoid using DDL (Data Definition Language) statements inside your stored procedure.

Using DDL statements inside stored procedures also reduce the chance to reuse the execution plan.

18) Add the WITH RECOMPILE option to the CREATE PROCEDURE statement if you know that your query will vary each time it is run from the stored procedure.

The WITH RECOMPILE option prevents reusing the stored procedure execution plan, so SQL Server does not cache a plan for this procedure and the procedure is always recompiled at run time. Using the WITH RECOMPILE option can boost performance if your query will vary each time it is run from the stored procedure, because in this case the wrong execution plan will not be used.

19) Use SQL Server Profiler to determine which stored procedures have been recompiled too often. To c

heck if a stored procedure has been recompiled, run SQL Server Profiler and choose to trace the event in the "Stored Procedures" category called "SP:Recompile". You can also trace the event "SP:StmtStarting" to see at what point in the procedure it is being recompiled. When you identify these stored procedures, you can take some correction actions to reduce or eliminate the excessive recompilations.

How to select a comma separated list and use it in WHERE IN clause

First create a user-defined function that retrieves a comma separated list of IDs from one table using COALESCE built-in function, sort of like I did here

How to combine several rows in one row, separated by commas

CREATE FUNCTION [dbo].[GetListOfIDs] ( )
RETURNS VARCHAR(8000)
AS
BEGIN
  DECLARE @RES VARCHAR(8000)
  SELECT @RES = COALESCE(@RES + ',','') + ID
  FROM Table1
  RETURN (@RES)
END

Then create a stored procedure that will retrieve all the data from another table, based on input list:

CREATE PROCEDURE [dbo].[usp_GetAllData]
@list varchar(500)
as
begin
declare @SQL varchar(600)
set @SQL = 'SELECT * FROM TABLE2 WHERE ID IN ('+ @list +')'
EXEC(@SQL)
end       

Then you will need one more function to tie it all together - use user-defined function to retrieve the list of IDs and then execute stored procedure and pass it the list:

CREATE PROCEDURE usp_RunProcedure
AS
BEGIN
DECLARE @ids varchar(600)  
SELECT @ids = dbo.GetListOfIDs()

EXEC dbo.usp_GetAllData @list=@ids
END

SQL Server 2005 Agent XPs disabled issue

Today when I got to work I got an e-mail sent to me through Database mail, notifying me that a number of SQL scheduled jobs failed. That's the message that I found in the logs:

[298] SQLServer Error: 15281, SQL Server blocked access to procedure 'dbo.sp_sqlagent_has_server_access' of component 'Agent XPs' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Agent XPs' by using sp_configure. For more information about enabling 'Agent XPs', see "Surface Area Configuration" in SQL Server Books Online. [SQLSTATE 42000] (ConnIsLoginSysAdmin)

When I logged in to database server, I saw that next to SQL Server Agent there is a note (Agent XPs disabled).

Agent XPs is an option to enable the SQL Server Agent extended stored procedures on this server. When this option is not enabled, the SQL Server Agent node is not available in SQL Server Management Studio Object Explorer.


The possible values are:

0, indicating that SQL Server Agent extended stored procedures are not available (the default).

1, indicating that SQL Server Agent extended stored procedures are available.


This setting can be changed without server stop or restart.

Aparently something happened, probably related to the Security policy, that changed the option from 1 to 0. So to resolve the issue I ran the code below to re-enable Agent XPs option by setting it back to 1.

USE master
GO
sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO
sp_configure 'Agent XPs', 1
GO
RECONFIGURE
GO

Another option is to re-enable it through SQL Server Surface Area Configuration tool.

How to get sql table column names based on column field value

There is no easy way to retrieve a list of columns from a particular SQL Server table based on the column values. The only way is to create a user-defined function or a stored procedure and then retrieve its results.

Here is an example of stored procedure that returns a comma separated list of table columns that contain a particular value.

CREATE PROCEDURE [dbo].[usp_FindColumnsContainingTheValue](@table_name nvarchar(50), @val nvarchar(50))
AS
BEGIN
DECLARE @column_name nvarchar(50)
DECLARE @column_list nvarchar(1000)
DECLARE @count int
DECLARE @sql nvarchar(1000)

DECLARE my_cursor CURSOR FOR
SELECT column_name 
FROM information_schema.columns
WHERE table_name=@table_name
AND data_type IN('nvarchar','varchar', 'ntext', 'nchar')

OPEN my_cursor

FETCH NEXT FROM my_cursor
INTO @column_name

SET @column_list=''

WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql=N'SELECT @countOUT=COUNT(*) FROM ' + @table_name + ' WHERE ' + @column_name + '=' + '''' + @val + ''''

EXEC sp_executesql 
        @query = @sql, 
        @params = N'@countOUT INT OUTPUT', 
        @countOUT = @count OUTPUT

 IF @count > 0 
 BEGIN
  IF LEN(@column_list) > 0
  BEGIN
   SET @column_list=@column_list + ',' + @column_name
  END
  ELSE
  BEGIN
   SET @column_list= @column_name
  END


 END
     FETCH NEXT FROM my_cursor
     INTO @column_name
END

CLOSE my_cursor
DEALLOCATE my_cursor
SELECT @column_list

END

As you can see, the stored procedure I wrote takes two parameters - table name and a value.

Now, as a way to illustrate on how this stored procedure can be used, I will take Northwind database table Orders and will use the above procedure to retrieve all the columns that contain value 'Brazil'.

EXECUTE dbo.usp_FindColumnsContainingTheValue 'Orders', 'Brazil'

Will return a single value 'ShipCountry', for it's the only column that contains a value 'Brazil' in one of the rows.

How to pass a table name as a parameter to a stored procedure

In order to pass table name along with some fields to a stored procedure that does insert, one has to take advantage of dynamic sql

CREATE PROCEDURE myProc
@TableName as VARCHAR(50),
@fieldlist as VARCHAR(100),
@ID as INT, 
@Name as VARCHAR(50)
AS 
DECLARE @sql VARCHAR(1000) 
DECLARE @str NVARCHAR(1000)

SET @sql = 'INSERT INTO ' + @TableName + '(' + @fieldlist + ')' + ' Values(@ID,@Name)'
Select @str = CAST(@sql as NVarchar(1000))
EXECUTE sp_executesql @str,N'@ID INT,@Name VARCHAR(50)', @ID, @Name

GO

The above stored procedure can be called with the following

EXEC myProc 'Table1', 'ID, Name' ,1,'Taylor Lautner'
GO