List all stored procedures in a database

This script will provide a list of all stored procedures in a database along with the create script for each.

SELECT specific_name, routine_definition 
  FROM information_schema.routines 
 WHERE routine_type = 'PROCEDURE'
 
[tweetmeme only_single=”false”]

How to find out what triggers you have.

I try to avoid using triggers because I always forget they are there.  I have been burned more than once by triggers updating data when I didn’t know they were there.  I have also been troubleshooting someone else’s database and been bitten by triggers.
The code from this post I copied from Chris McGowan’s blog.  He wrote up a great article about trigger status.  His post has code to create a job to email you.
I modified it to just run in SSMS and use a table variable (in case you don’t have the permissions to create a temp table).

This script runs against master and will tell you about all triggers in all databases.

Use master

-- Create table variable
DECLARE @TriggerStatus as Table
(
      DatabaseName SYSNAME,
      TableName VARCHAR(255), 
      TriggerName VARCHAR(255),
      TriggerStatus VARCHAR(8)
);
-- Insert triggers
INSERT INTO @TriggerStatus
EXEC sp_msforeachdb
'
IF ''?'' NOT IN (''master'', ''model'', ''msdb'', ''tempdb'', ''distribution'', ''reportserver'', ''reportservertempdb'')
BEGIN
USE [?]; 
SELECT  DB_NAME() AS DatabaseName,
        OBJECT_NAME(parent_id) AS TableName,
        name AS TriggerName,
        CASE is_disabled
          WHEN 0 THEN ''Enabled''
          ELSE ''Disabled''
        END AS TriggerStatus
FROM    sys.triggers WITH ( NOLOCK )
WHERE   is_ms_shipped = 0
        AND parent_class = 1;
END'

SELECT DatabaseName, TableName, TriggerName,  TriggerStatus FROM   @TriggerStatus 

[tweetmeme only_single=”false”]

What little I know about SQL Bulk Insert

I’ve got a stored procedure to upload a csv file to a database table.  From what I’ve read that’s the fastest way to load csv data to SQL.

Cool, but I’ve had errors when I went to deploy to production.

Here are just a few things I’ve learned.
1. The permissions used are different when using windows login vs sql login (to call the stored procedure).
2. If you use a windows login to call the stored procedure it will use the permissions of the user to read the file (no matter what you what the SQL process is set to run).
3. If you use a SQL login then account the SQL process is setup to run as is used.
4.  Bulk Insert doesn’t like to pull data from any server,  but the server that SQL is running on.  If you do try pulling data from another server then make sure you use a UNC path.

After all that I still had permissions trouble.
So my application copied the csv file to a directory on the SQL server.  Then calling the procedure to do the Bulk Insert worked without issue.

Below is my stored procedure.  You just need to pass it the path to the file you want to import.
It will load it to a table dbtemp.
Make sure that table has the same fields as your source file.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[sp_ImportFile] 
    @filename varchar(1000) 

AS
BEGIN

    SET NOCOUNT ON;

declare @sql varchar(4000)
select @sql = '
BULK
INSERT dbtemp
FROM ''' + @filename + '''
WITH
(
FIELDTERMINATOR = ''","'',
FIRSTROW = 2,
ROWTERMINATOR = ''\n''
)'
exec (@sql)
update dbtemp set funding = REPLACE(funding,'"',''), initialdate = REPLACE(initialdate,'"','')

END
[tweetmeme only_single=”false”]

Undocumented stored procedure for looping through all tables in a database

I got tired of writing code to loop through all tables in a database.  I found out about an undocumented stored procedure that does most of that for me.
The stored procedure name is sp_MSforeachtable it takes one input parameter (@command).
This simplifies so much code (no more looping through sysobjects).

Here are some examples of how to use it.

This command will return a row count for each table in your database.

EXEC sp_MSforeachtable ‘SELECT ‘‘?’‘, Count(*) as NumberOfRows FROM ?’

Notice the question mark (?), that gets substituted as the table (in the sp).
So basically any command you can do against a table you can use and put a ? where you put a table name.

Here is how to rebuild all indexes.

EXEC sp_MSforeachtable @command1="print '?' DBCC DBREINDEX ('?', ' ', 80)"

Here is how you update all statistics on your tables.

EXEC sp_MSforeachtable 'UPDATE statistics ? WITH ALL'

You can also add additional conditional logic if you need to restrict your commands to a subset of tables.
This is very powerful. You don’t always need to hit all tables with a command.

This one will loop through all your tables and add the Column CreatedOn on any table that doesn’t already have it.

EXEC sp_MSforeachtable '
    if not exists (select column_name from INFORMATION_SCHEMA.columns 
                   where table_name = ''?'' and column_name = ''CreatedOn'') 
    begin
        ALTER TABLE [?] ADD CreatedOn datetime NOT NULL DEFAULT getdate();
    end
'
[tweetmeme only_single=”false”]

Entity Framework call stored proc with code

If you need to call a stored procedure from Entity framework and you can’t add it to the designer (like if it doesn’t return an entity)  you can do with just code.
This way you can use the same connection as you model.

Here is some example code to use.

 using (cctEntities ccte = new cctEntities ())
                {
                    DbConnection connection = ((EntityConnection)ccte.Connection).StoreConnection;
                    bool opening = (connection.State == ConnectionState.Closed);
                    if (opening)
                    {
                        connection.Open();
                    }

                    DbCommand cmd = connection.CreateCommand();

                    cmd.CommandText = "proc_SelectMarketInfo";
                    cmd.CommandType = CommandType.StoredProcedure;

                    cmd.Parameters.Add(new SqlParameter("@MarketID", MarketID));

                    try
                    {
                        using (DbDataReader dReader = cmd.ExecuteReader())
                        {
                            //Read sproc results
                            while (dReader.Read())
                            {
                                UserRole U = new UserRole();
                                U.userFirst = String.IsNullOrEmpty(dReader["FirstName"].ToString()) ? string.Empty : dReader["FirstName"].ToString();
                                U.userLast = String.IsNullOrEmpty(dReader["LastName"].ToString()) ? string.Empty : dReader["LastName"].ToString();
                                U.username = String.IsNullOrEmpty(dReader["Username"].ToString()) ? string.Empty : dReader["Username"].ToString();
                                U.userRole = String.IsNullOrEmpty(dReader["Role"].ToString()) ? string.Empty : dReader["Role"].ToString();
                                markets.Add(U);
                            }
                        }
                    }
                    finally
                    {
                        if (opening && connection.State == ConnectionState.Open)
                        {
                            connection.Close();

                        }
                    }
                }

.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, “Courier New”, courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }

Grant execute permissions to all stored procs easily

Granting execute permissions can be stored procedures can be quite a pain, especially when you have dropped and recreated your stored procedures when moving between environments.

You can go to each stored procedure and right click each one and change the permissions.  That takes forever.

A quick way to resolve the issue (in SQL 2005 and SQL 2008) is to create a role and give it execute permissions at that database level.

Here is the SQL to run:

/* CREATE A NEW ROLE */
CREATE ROLE db_executor

/* GRANT EXECUTE TO THE ROLE */
GRANT EXECUTE TO db_executor

 

This will create a SQL role and anyone you put in it will will have execute permissions to all stored procedures in the database.

This doesn’t work if you only want to grant execute to a subset of stored procedures.

Technorati Tags:

SSIS Error using a stored proc as source

SSIS is a great tool to move data around.  Sometimes it’s easier to use a stored proc to pull together some data before you do stuff with it.

To use a stored procedure you can use an OLE DB source (in a Data Flow task) SQL command.

Works great, but if it doesn’t and you get the error code 0xC02092B4, you will have to add to your stored procedure.

Just add the following 2 lines to the top of your stored procedure:

SET NOCOUNT ON
SET FMTONLY OFF

This will clear up the metadata issues with SSIS trying to understand what your stored procedures outputs.

Dynamic searches without dynamic sql

Search functionality is a feature that comes up with many applications.  I have written more than one search stored procedure (and screen), but I usually ended up with writing dynamic sql (building a SQL statement and using the exec command, see Figure 1 below).  The main disadvantage to dynamic sql statements is that they are not compiled and performance always matters.

I was reading CoDe magazine one day and noticed a part of an article that mentioned dynamic sql without dynamic sql.  I read it and was quite pleased with the approach.  So the basic approach is to have all the lookup parameters as optional parameters, set null parameters to the % wildcard, use a series of like statements in the where clause.

See figure 2 for an example of the sql code.

Figure 1

Create Procedure Search
    @ProductName VarChar(100)
AS

Declare @SQL VarChar(1000)

Select @SQL = 'SELECT * FROM Products '
Select @SQL = @SQL + 'WHERE ProductID in (' + @ProductName +')'

Exec ( @SQL)

Figure 2
Create Procedure Search
@ProductName varchar(100) = null
As
Set @ProductName = isnull(@ProductNmae, '%')

Select * from Products
where ProductName like @ProductName