Author: user

SQL Server For Each Procedure

I created my sp_ForEachProcedure base on Microsoft undocumented sp_msForEachTable.  It doesn’t have all the features of sp_msForEachTable, but does include a few of them.  I even added a few custom parameters (@print, @execute).   I did dynamic SQL because I was having problems creating the where statement.  I believe I could use a case, but went ahead and used the dynamic sql.  I would like to join to syscomments and search the actual stored procedure, but ran out of time. 
 
In the future I plan on creating sp_ForEachView
 
 
 

–example of sp_msforeachtable

–exec sp_msforeachtable @command1=’PRINT ”?”’, @whereand=’ and o.name like ”%Human%”’

 
ALTER

PROCEDURE sp_ForEachProcedure

  @command1

VARCHAR(MAX),

  @whereand

VARCHAR(MAX) = NULL,

  @print

BIT = 1,

  @execute

BIT = 0

AS

–DECLARE @command1 VARCHAR(MAX)
–DECLARE @whereand VARCHAR(MAX)
–SET @command1 = ‘PRINT ”?”’
–SET @whereand = ‘name like ”%contact%”’
 

DECLARE

@executeCommand NVARCHAR(MAX)

SET

@executeCommand =

  ‘ DECLARE @statementTable TABLE (ID INT IDENTITY(1,1) ,statement NVARCHAR(MAX)) ‘

+

  ‘ DECLARE @userCommand NVARCHAR(MAX) ‘

+

  ‘ INSERT INTO @statementTable (statement) ‘

+

  ‘ SELECT ‘

+

  ‘ REPLACE(”’

+ REPLACE(@command1, ””, ”””) + ”’, ”?”, ”[” + p.name + ”]”)’ + ‘ AS Statement’ +

  ‘ FROM SYS.Procedures p ‘

IF

(@whereand IS NOT NULL)

BEGIN

  SET @executeCommand = @executeCommand +

  ‘ WHERE ‘

+ REPLACE(@whereand, ”, ””)

END

IF

(@print = 1)

BEGIN

  SET @executeCommand = @executeCommand + ‘ SELECT * FROM @statementTable’

END
IF

@execute = 1

BEGIN

  SET @executeCommand = @executeCommand +

  ‘ DECLARE @loopCount INT ‘ +

  ‘ DECLARE @statement NVARCHAR(MAX) ‘ +

  ‘ SELECT @loopCount = COUNT(*)FROM @statementTable ‘ +

  ‘ WHILE @loopCount <> 0 ‘ +

  ‘ BEGIN ‘ +

  ‘ SET @statement = (SELECT statement FROM @statementTable WHERE ID = @loopCount) ‘ +

  –‘ PRINT ”EXECUTING: ” + @Statement ‘ +

  ‘ EXEC sp_executesql @Statement ‘ +

  ‘ SET @loopCount = @loopCount – 1 ‘ +

  ‘ END ‘

END

–FOR DEBUGGING
–PRINT @executeCommand

EXEC

sp_executesql @executeCommand

GO
 

–sp_ForEachProcedure ‘PRINT ”?”’, ‘NAME LIKE ”%Human%”’, 1
–sp_ForEachProcedure ‘GRANT EXECUTE ON ? TO Scrub ‘, ‘NAME LIKE ”%Human%”’, 1, 1
 
References:
Is a Temporary Table Really Necessary (How not to use cursors):

SQL Server Shrink and Truncating Database, Log and Indexes

I ran into problems compare two 8 gig plus database, but data wasn’t important.  I wanted to clear all the data from the database and use SQL Compare to show the difference. And make changes where needed.  There were situations where I needed to restore the database(s) and at 15 minutes it took pretty long.  I decided to clear all the data from the database and do a backup of the empty database and restore the empty databases instead.  This seems like a easy endeavor, but took me a while to figure it out. With that said,here’s how to Shrink and Trunate Database, Logs and Indexes
 
Very little of the following code is my own.  I used the resources at the end of the blog to create the following statement.
 
EXEC

sp_MSForEachTable ‘ALTER TABLE ? NOCHECK CONSTRAINT ALL’

GO

EXEC

sp_MSForEachTable ‘ALTER TABLE ? DISABLE TRIGGER ALL’

GO

EXEC

sp_MSForEachTable

  ‘BEGIN TRY

  TRUNCATE TABLE ?
  END TRY
  BEGIN CATCH
  DELETE FROM ?
  END CATCH;’

GO

EXEC

sp_MSForEachTable ‘ALTER TABLE ? CHECK CONSTRAINT ALL’

GO

EXEC

sp_MSForEachTable ‘ALTER TABLE ? ENABLE TRIGGER ALL’

GO

–DBCC LOGINFO

EXEC

sp_MSforeachtable @command1="print ‘?’ DBCC DBREINDEX (‘?’, ‘ ‘, 100)"

 

DBCC

SHRINKFILE(DataFileName, 1)— OR DBCC SHRINKDATABASE (DatabaseName, 1)

BACKUP

LOG DatabaseName WITH TRUNCATE_ONLY

DBCC

SHRINKFILE(DatabaseLogName, 1)

 
–INFO

DBCC

SQLPERF(logspace)

EXEC

sp_spaceused

DBCC

LogInfo

 
 
Simple example of sp_msForEachTable:

exec

sp_msforeachtable @command1=‘PRINT ”?”’, @whereand=‘ and o.name like ”%Loan_%”’

 
Resource:
SQL SERVER - Shrinking Truncate Log File - Log Full
 
Introduction to the Transaction Log
 

Great Article Why you want to be restrictive with shrink of database files
 
How to determine SQL Server database transaction log usage
 
Reducing SQL Server Index Fragmentation
 
How to: Truncate Multiple Tables In SQL Server and the magic of sp_MSforeachtable
 
SQL Server Undocumented Stored Procedures sp_MSforeachtable and sp_MSforeachdb (Great Article)

SQL – Using COALESCE to Build Comma-Delimited String

I had a query that returned one column with multiple records.  I wanted only one record with all the values to be comma separated.  I could do this with a cursor, but that throws a big warning sign.  So i decided to use the COALESCE function.  Below are some of the resources i used to solve my problem.
 
 

–**************************************************************************
–http://www.sqlteam.com/article/using-coalesce-to-build-comma-delimited-string

DECLARE

@EmployeeList varchar(100)

SELECT

@EmployeeList = COALESCE(@EmployeeList + ‘, ‘, ”) +

CAST(Emp_UniqueID AS varchar(5))

FROM

SalesCallsEmployees

WHERE

SalCal_UniqueID = 1

SELECT

@EmployeeList

–**************************************************************************
–http://www.sqlservercentral.com/scripts/Miscellaneous/31922/

create

procedure sp_return_students

as
set

nocount off

/* Declare variable which will store all student name */

Declare

@StudentName varchar(8000)

/* Query that will return student names and at the same time concatenate values. */

select

@StudentName = coalesce(@StudentName + ‘, ‘, ”) + stu_name from tbl_students

/* At last, you just have to define column name that will store values */

Select

@StudentName As Student

How to find SQL Relationship and Dependencies (Dependency)

 
 
SQL SERVER – Query to display Foreign Key relationships and name of the constraint for each table in Database.
I tested the query at the following website and it seems to work.  In the response section someone claims to have modified the query and made it faster.  A where can be added to it easily to limit it to a specific table.
 
This is a cool system Stored Procedrue, but I believe it’s not documented.
EXEC sp_MSdependencies N’mo_company’, null, 1315327
 
System Stored Procedure that is documented, but doesn’t give you what you really want. But kinda cool.
EXEC sp_depends @objname = N’tablename’
 
*****************************************************************************************
EXEC sp_depends @objname = N’tablename’
 
EXEC sp_MSdependencies N’tablename’, null, 1315327
*****************************************************************************************
SQL SERVER – Query to display Foreign Key relationships and name of the constraint for each table in Database.
This is very long query. Optionally, we can limit the query to return results for one or more than one table.

SELECT
     K_Table  = FK.TABLE_NAME,
     FK_Column = CU.COLUMN_NAME,
     PK_Table  = PK.TABLE_NAME,
     PK_Column = PT.COLUMN_NAME,
     Constraint_Name = C.CONSTRAINT_NAME
FROM       INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C
INNER JOIN  INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK ON C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME
INNER JOIN      INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK ON C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME
INNER JOIN      INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU ON C.CONSTRAINT_NAME = CU.CONSTRAINT_NAME
INNER JOIN  (
     SELECT      i1.TABLE_NAME, i2.COLUMN_NAME
     FROM        INFORMATION_SCHEMA.TABLE_CONSTRAINTS i1
         INNER JOIN      INFORMATION_SCHEMA.KEY_COLUMN_USAGE i2 ON i1.CONSTRAINT_NAME = i2.CONSTRAINT_NAME
             WHERE       i1.CONSTRAINT_TYPE = ‘PRIMARY KEY’
     ) PT ON PT.TABLE_NAME = PK.TABLE_NAME
—- optional:
ORDER BY
     1,2,3,4
WHERE      PK.TABLE_NAME=’something’WHERE      FK.TABLE_NAME=’something’

WHERE      PK.TABLE_NAME IN (‘one_thing’, ‘another’)

WHERE      FK.TABLE_NAME IN (‘one_thing’, ‘another’)

*****************************************************************************************

SELECT
CONSTRAINT_NAME = REF_CONST.CONSTRAINT_NAME,
TABLE_CATALOG = FK.TABLE_CATALOG,
TABLE_SCHEMA = FK.TABLE_SCHEMA,
TABLE_NAME = FK.TABLE_NAME,
COLUMN_NAME = FK_COLS.COLUMN_NAME,
REFERENCED_TABLE_CATALOG = PK.TABLE_CATALOG,
REFERENCED_TABLE_SCHEMA = PK.TABLE_SCHEMA,
REFERENCED_TABLE_NAME = PK.TABLE_NAME,
REFERENCED_COLUMN_NAME = PK_COLS.COLUMN_NAME
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS REF_CONST
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK
ON REF_CONST.CONSTRAINT_CATALOG = FK.CONSTRAINT_CATALOG
AND REF_CONST.CONSTRAINT_SCHEMA = FK.CONSTRAINT_SCHEMA
AND REF_CONST.CONSTRAINT_NAME = FK.CONSTRAINT_NAME
AND FK.CONSTRAINT_TYPE = ‘FOREIGN KEY’
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK ON REF_CONST.UNIQUE_CONSTRAINT_CATALOG = PK.CONSTRAINT_CATALOG
AND REF_CONST.UNIQUE_CONSTRAINT_SCHEMA = PK.CONSTRAINT_SCHEMA
AND REF_CONST.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME
AND PK.CONSTRAINT_TYPE = ‘PRIMARY KEY’
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE FK_COLS ON REF_CONST.CONSTRAINT_NAME = FK_COLS.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE PK_COLS ON PK.CONSTRAINT_NAME = PK_COLS.CONSTRAINT_NAME

SQL Server Scripts to Copy User Permission

I have the need to copy user permissions from one user to another.
 
I have two SQL Server database. The structure of both database are the same.  The first db is a source database with permission for set for a specific user (User1).  The second database has a new user (User2) with no permissions.  I want user2 have the same permission of user1.  The following links/scripts allowed me to copy user permissions.
 

/* ********************************************
******************************************** */

SET

NOCOUNT ON

DECLARE

@OldUser sysname, @NewUser sysname

SET

@OldUser = ‘User1

SET

@NewUser = ‘User2’

SELECT

‘USE’ + SPACE(1) + QUOTENAME(DB_NAME()) AS ‘–Database Context’

 

SELECT

‘–Cloning permissions from’ + SPACE(1) + QUOTENAME(@OldUser) + SPACE(1) + ‘to’ + SPACE(1) + QUOTENAME(@NewUser) AS ‘–Comment’

 
SELECT

‘EXEC sp_addrolemember @rolename =’

+ SPACE(1) + QUOTENAME(USER_NAME(rm.role_principal_id), ””) + ‘, @membername =’ + SPACE(1) + QUOTENAME(@NewUser, ””) AS ‘–Role Memberships’

FROM

sys.database_role_members AS rm

WHERE

USER_NAME(rm.member_principal_id) = @OldUser

ORDER

BY rm.role_principal_id ASC

 
SELECT

CASE WHEN perm.state <> ‘W’ THEN perm.state_desc ELSE ‘GRANT’ END

+ SPACE(1) + perm.permission_name + SPACE(1) + ‘ON ‘ + QUOTENAME(USER_NAME(obj.schema_id)) + ‘.’ + QUOTENAME(obj.name)

+ CASE WHEN cl.column_id IS NULL THEN SPACE(0) ELSE ‘(‘ + QUOTENAME(cl.name) + ‘)’ END

+ SPACE(1) + ‘TO’ + SPACE(1) + QUOTENAME(@NewUser) COLLATE database_default

+ CASE WHEN perm.state <> ‘W’ THEN SPACE(0) ELSE SPACE(1) + ‘WITH GRANT OPTION’ END AS ‘–Object Level Permissions’

FROM

sys.database_permissions AS perm

INNER JOIN

sys.objects AS obj

ON perm.major_id = obj.[object_id]

INNER JOIN

sys.database_principals AS usr

ON perm.grantee_principal_id = usr.principal_id

LEFT JOIN

sys.columns AS cl

ON cl.column_id = perm.minor_id AND cl.[object_id] = perm.major_id

WHERE

usr.name = @OldUser

ORDER

BY perm.permission_name ASC, perm.state_desc ASC

 
SELECT

CASE WHEN perm.state <> ‘W’ THEN perm.state_desc ELSE ‘GRANT’ END

+ SPACE(1) + perm.permission_name + SPACE(1)

+ SPACE(1) + ‘TO’ + SPACE(1) + QUOTENAME(@NewUser) COLLATE database_default

+ CASE WHEN perm.state <> ‘W’ THEN SPACE(0) ELSE SPACE(1) + ‘WITH GRANT OPTION’ END AS ‘–Database Level Permissions’

FROM

sys.database_permissions AS perm

INNER JOIN

sys.database_principals AS usr

ON perm.grantee_principal_id = usr.principal_id

WHERE

usr.name = @OldUser

AND

perm.major_id = 0

ORDER

BY perm.permission_name ASC, perm.state_desc ASC

 
 

/* ********************************************
******************************************** */

SELECT
CASE

protecttype WHEN 204 THEN ‘GRANT_W_GRANT ‘

WHEN 205 THEN ‘GRANT ‘

WHEN 206 THEN ‘REVOKE ‘

ELSE

‘DUMMY GRANT’ END

+

CASE action WHEN 26 THEN ‘REFERENCES’

WHEN 178 THEN ‘CREATE FUNCTION’

WHEN 193 THEN ‘SELECT’

WHEN 195 THEN ‘INSERT’

WHEN 196 THEN ‘DELETE’

WHEN 197 THEN ‘UPDATE’

WHEN 198 THEN ‘CREATE TABLE’

WHEN 203 THEN ‘CREATE DATABASE’

WHEN 207 THEN ‘CREATE VIEW’

WHEN 222 THEN ‘CREATE PROCEDURE’

WHEN 224 THEN ‘EXECUTE’

WHEN 228 THEN ‘BACKUP DATABASE’

WHEN 233 THEN ‘CREATE DEFAULT’

WHEN 235 THEN ‘BACKUP LOG’

WHEN 236 THEN ‘CREATE RULE’

ELSE ‘ DUMMY ‘ END + ‘ ON ‘

+

so.name + ‘ TO <Your Role/User on Target Server>’

from

sysprotects sp

inner

join sysobjects so on (so.id=sp.id)

inner

join sysusers s on (sp.uid=s.uid)

WHERE

s.[name] in (‘User1’)

 
 
 

/* ******************************************************
The following Script just provides a select of the previous script
****************************************************** */

SELECT
CASE protecttype WHEN 204 THEN ‘GRANT_W_GRANT ‘

WHEN 205 THEN ‘GRANT ‘

WHEN 206 THEN ‘REVOKE ‘

ELSE ‘DUMMY GRANT’

END

,CASE action WHEN 26 THEN ‘REFERENCES’

WHEN 178 THEN ‘CREATE FUNCTION’

WHEN 193 THEN ‘SELECT’

WHEN 195 THEN ‘INSERT’

WHEN 196 THEN ‘DELETE’

WHEN 197 THEN ‘UPDATE’

WHEN 198 THEN ‘CREATE TABLE’

WHEN 203 THEN ‘CREATE DATABASE’

WHEN 207 THEN ‘CREATE VIEW’

WHEN 222 THEN ‘CREATE PROCEDURE’

WHEN 224 THEN ‘EXECUTE’

WHEN 228 THEN ‘BACKUP DATABASE’

WHEN 233 THEN ‘CREATE DEFAULT’

WHEN 235 THEN ‘BACKUP LOG’

WHEN 236 THEN ‘CREATE RULE’

ELSE ‘ DUMMY ‘

END

,*

from

sysprotects sp

inner

join sysobjects so on (so.id=sp.id)

inner

join sysusers s on (sp.uid=s.uid)

WHERE

s.[name] in (‘User1’)

 
 

Advanced Regular Expression – Groups Name Capture LookAround

 
Zip Code Regular Expression
^([0-9]{5}-?([0-9]{4})?){1}$
 
I’ve been doing alot of regular expression lately and need some place to put my resources.  Below are some very good links for regular Expressions.
 
How to validate a valid GUID Value in C#
 
Password Regex
 

Python Library Reference

http://www.python.org/doc/current/lib/re-syntax.html

 

Groups, Name Capture, LookAround, Readability and Maintainability, and Delegates

 
One of the better website pertaining to Regular Expression
 
Very Simple example of Named Groups, Backreferences, and Regex.Replace
 
MSDN Article on Backreferences
 
Regular Expression Replace in SQL 2005 (via the CLR)
 
SQL Server 2005 Regular Expressions Make Pattern Matching And Data Extraction Easier
 
A T-SQL Regular Expression Library for SQL Server 2005
 
 
Regular Expression Cheat Sheat

generate insert script sql server

I’m using a backup database to do some testing.  This database already has data in it, but I would Like to add some additional data to it, possibly through a use rinterface and scripts.  If everything works well, I want to add the data that I added to backup database to another database.  I don’t want to have to do an export or Bulk Copy.  It seems like to many steps.  What I would rather have is just an insert statement the would crate the data. 
 
The following links are for script that generate insert statements:
 
This one is very simple, but I needed to change nvarchar(…)s to nvarchar(Max).
 
Here are some more links generate insert script for sql server
 
 
 

SQL Server Cursors With Dynamic SQL

First of all I do not recommend using Dynamic SQL or Cursors in production queries or stored procedures.  But they are a necessary evil sometimes.  When I do administrative queries or sp, dynamic SQL and cursors are very handy.
 
For example I needed to populate a table from meta data in two different tables.  I need the meta data to figure out what tables are needed to be use as a source for my query.  I have to create dynamic query, because at the time of execurting the stored procedure I don’t know which table and column should be used as the source.   After creating the dynamic SQL statement, I then need to loop through the results and create an insert statement for each record returned from the cursor.  Right now I won’t go into detail of the solution, but the follwowing code example helped alot.

[sourcecode language=”sql”]
DECLARE @my_cur CURSOR
EXEC sp_executesql
N’SET @my_cur = CURSOR STATIC FOR
SELECT name FROM dbo.sysobjects;
OPEN @my_cur’
,N’@my_cur cursor OUTPUT’, @my_cur OUTPUT
FETCH
NEXT FROM @my_cur
[/sourcecode]