x002548's Blog

How can I update Multiple Tables at once

Brett Kaiser (x002548) — Thu, 03 Apr 2008 13:49:05 GMT

"CAN'T BE DONE" -- Crazy boy

Well at first glance, that may very well be the answer, as in this thread:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=100207

Now, this might not be what the OP is looking for, but if you employ a partitioned view, then yes, it's doable

cut and paste the sample code to see it in action

USE Northwind
GO

CREATE TABLE myTable99 (Col1 int PRIMARY KEY CHECK (Col1 BETWEEN 1 AND 10), Col2 varchar(50))
CREATE TABLE myTable98 (Col1 int PRIMARY KEY CHECK (Col1 BETWEEN 11 AND 20), Col2 varchar(50))
GO

INSERT INTO myTable99(Col1, Col2)
SELECT 1, 'x' UNION ALL
SELECT 2, 'y' UNION ALL
SELECT 3, 'z'

INSERT INTO myTable98(Col1, Col2)
SELECT 11, 'x' UNION ALL
SELECT 12, 'y' UNION ALL
SELECT 13, 'z'
GO

CREATE VIEW myView99
AS
SELECT Col1, Col2 FROM myTable99
UNION ALL
SELECT Col1, Col2 FROM myTable98
GO

SELECT * FROM myView99

UPDATE myView99 SET Col2 = 'x002548' WHERE Col2 = 'z'

SELECT * FROM myView99

/*
DROP VIEW myView99
DROP TABLE myTable99, myTable98
*/

Because you're mine, I walk the line

Brett Kaiser (x002548) — Thu, 26 Jul 2007 19:45:36 GMT

....ummm, well, that "line" would be walking through a line in a family tree. I saw a post the other the day that asked, if I know a relative somewhere in a families "lineage", how can I find the entire family tree from top to bottom.

Well here's a hack to do that...don't ask for effecienciey. This for SQL Server 2000. I have to determine if CTE's in 2k5 can come up with a better solution, but here it is for now.

CREATE TABLE Parent (
ID_PK int IDENTITY(1,1)
, [Name] varchar(20)
, PhoneNum varchar(20)
, Address varchar(30))

CREATE TABLE Child (
ID_PK int
, ParentID_FK int)
GO

INSERT INTO Parent([Name],PhoneNum, Address)
SELECT 'Annie',     '111-111-1111', '1st Street' UNION ALL
SELECT 'Bob',       '222-222-2222', '2nd Street' UNION ALL
SELECT 'Cathy',     '333-333-3333', '3rd Street' UNION ALL
SELECT 'Don',       '444-444-4444', '4th Street' UNION ALL
SELECT 'Emily',     '555-555-5555', '5th Street' UNION ALL
SELECT 'Frank',     '666-666-6666', '6th Street' UNION ALL
SELECT 'Georgette', '777-777-7777', '7th Street' UNION ALL
SELECT 'Harry',     '888-888-8888', '8th Street'

INSERT INTO Child(ID_PK, ParentID_FK)
SELECT 1, null UNION ALL
SELECT 2, 1    UNION ALL
SELECT 3, 2    UNION ALL
SELECT 4, 3    UNION ALL
SELECT 5, null UNION ALL
SELECT 6, 5    UNION ALL
SELECT 7, 6    UNION ALL
SELECT 8, 7
GO

SELECT * FROM Parent p LEFT JOIN Child c ON p.ID_PK = c.ID_PK
GO

CREATE FUNCTION udf_FindTree (@Child varchar(20))
RETURNS varchar(8000)
AS
BEGIN
DECLARE @p int, @p_save int, @rs varchar(8000)
SELECT @p = 0, @p_save = 0
SELECT @p = ParentID_FK FROM Child c JOIN Parent p ON c.ParentID_FK = p.ID_PK
WHERE [Name] = @Child
--Loop Until @@rowcount = 0
WHILE Exists (SELECT ParentID_FK FROM Child c WHERE ID_PK = @p)
BEGIN
SELECT @p_save = @p
SELECT @p = ParentID_FK FROM Child c WHERE ID_PK = @p_save
-- The Last assignement is the top Parent
END
--Now Walk from the top Down until @@rowcount = 0
SELECT @p = @p_save
SELECT @rs = [Name] + ' ' + PhoneNum + ' ' + Address FROM Parent WHERE ID_PK = @p
WHILE EXISTS (SELECT ID_PK FROM Child WHERE ParentID_FK = @p)
BEGIN
SELECT @rs = @rs + ' ' + COALESCE([Name],'') FROM Parent WHERE ID_PK = @p
SELECT @p = ID_PK FROM Child WHERE ParentID_FK = @p
END
RETURN @rs
END
GO

SELECT dbo.udf_FindTree('Cathy')
GO

SELECT * FROM Child c JOIN Parent p ON c.ParentID_FK = p.ID_PK
WHERE [Name] = 'Cathy'
GO

CREATE PROC usp_FindTree @Child varchar(20)
AS
SET NOCOUNT ON
DECLARE @p int, @p_save int, @rs varchar(8000)
SELECT @p = 0, @p_save = 0
SELECT @p = ParentID_FK FROM Child c JOIN Parent p ON c.ParentID_FK = p.ID_PK
WHERE [Name] = @Child
--Loop Until @@rowcount = 0
WHILE Exists (SELECT ParentID_FK FROM Child c WHERE ID_PK = @p)
BEGIN
SELECT @p_save = @p
SELECT @p = ParentID_FK FROM Child c WHERE ID_PK = @p_save
-- The Last assignement is the top Parent
END
--Now Walk from the top Down until @@rowcount = 0
SELECT @p = @p_save
SELECT @rs = [Name] + ' ' + PhoneNum + ' ' + Address FROM Parent WHERE ID_PK = @p
WHILE EXISTS (SELECT ID_PK FROM Child WHERE ParentID_FK = @p)
BEGIN
SELECT @rs = @rs + ' ' + COALESCE([Name],'') FROM Parent WHERE ID_PK = @p
SELECT @p = ID_PK FROM Child WHERE ParentID_FK = @p
END
SELECT @rs AS rs
SET NOCOUNT OFF
GO

EXEC usp_FindTree 'Cathy'
GO

DROP PROC usp_FindTree
DROP Function udf_FindTree
DROP TABLE Parent, Child
GO

Alias to be or not to be

Brett Kaiser (x002548) — Tue, 27 Mar 2007 18:59:43 GMT

EDIT: This is bizzare..I ran multiple table joins, and reran it ovr and over, and the times are always different and one time one is faster than the othe and other times it's the other wway around.

We were having a discussion over at SQLTeam on whether to use full table name aliases or short aliases to label columns. It always seemed to be a mattter of preference and debate on how self docuenting the code. For m I will always use short aliases and make sure I lable every column, even if it's unique, just so when I come back to the code I don't have to guess or go to the data model. Some suggest that that's not good enough and fully qualify the columns with the name. I think that is overkill, but hey, to each his or her own.

Then we got into a discussion about performance and if there was any. My first thought was, no way...then someone asked if anyone had ever tested it...so I got to thinking...ys, yes, I know, a dangerous proposition. And while this is not diffinitive by any means, and I've seen varying results, but the table without the aliases at all took longer. I have to run some more complicated tests, but this is what we get from the following code.

ShortLabelTime
--------------
93

FullLabelTime
-------------
93

NoLableTime
-----------
126

USE Northwind
GO

SET NOCOUNT ON
CREATE TABLE myTable99 (Col1 int IDENTITY(1,1), Col2 char(1), Col3 datetime DEFAULT(GetDate()))
GO

DECLARE @x int
SELECT @x = 1
WHILE @x < 10000
BEGIN
INSERT INTO myTable99(Col2) SELECT 'x'
SELECT @x = @x + 1
END
GO

DECLARE @s datetime

DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE
SELECT @s = GetDate()
SELECT a.Col1, a.Col2, a.Col3 FROM myTable99 a WHERE Col1 = 5000
SELECT DATEDIFF(ms,@s,GetDate()) AS ShortLabelTime

DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE
SELECT @s = GetDate()
SELECT myTable99.Col1, myTable99.Col2, myTable99.Col3 FROM myTable99 WHERE Col1 = 5000
SELECT DATEDIFF(ms,@s,GetDate()) AS FullLabelTime

DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE
SELECT @s = GetDate()
SELECT Col1, Col2, Col3 FROM myTable99 WHERE Col1 = 5000
SELECT DATEDIFF(ms,@s,GetDate()) AS NoLableTime
GO

SET NOCOUNT OFF
DROP TABLE myTable99
GO

Add Foreign Keys Back to the Database

Brett Kaiser (x002548) — Thu, 01 Mar 2007 14:49:24 GMT

"OK Brett, Now that I Removed all my Foreign Keys to Truncate the Data, Now What? I'm Hosed! Thanks a bunch"

OK, Well....sorry to keep you hangng out there. But, if you followed the code in the above link you will have all of the RI saved to the work table, so now all you need to do is replay it. The following is the code that will do this for you. Again, sorry for the delay.

CREATE procedure isp_exec_FK_code

AS

DECLARE @FKcode nvarchar(3000)
DECLARE @error_out int, @Result_Count int, @Error_Message varchar(255), @Error_Type int, @Error_Loc int, @rc int
SET NOCOUNT ON
SELECT @rc = 0
DECLARE FKcode cursor fast_forward read_only for

SELECT * FROM FK_Create_code

OPEN FKcode

FETCH NEXT FROM FKcode
INTO @FKcode

WHILE @@fetch_status = 0
BEGIN
execute sp_executesql @FKcode
Select @error_out = @@error
If @error_out <> 0
  BEGIN
   SELECT @Error_Loc = 1, @Error_Type = 50001, @rc = -1
   GOTO isp_exec_FK_code_Error
  END
FETCH NEXT FROM FKcode
INTO @FKcode
END

isp_exec_FK_code_Exit:
CLOSE FKcode
DEALLOCATE FKcode
SET NOCOUNT OFF
RETURN @rc

isp_exec_FK_code_Error:

If @Error_Type = 50001
BEGIN
  Select @error_message = (Select 'Location: ' + ',"' + RTrim(Convert(char(3),@Error_Loc))
          + ',"' + ' @@ERROR: ' + ',"' + RTrim(Convert(char(6),error))
          + ',"' + ' Severity: ' + ',"' + RTrim(Convert(char(3),severity))
          + ',"' + ' Message: ' + ',"' + RTrim(description)
          From master..sysmessages
        Where error = @error_out)
END

RAISERROR @Error_Type @Error_Message

GOTO isp_exec_FK_code_Exit

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

Collecting Requirements For Key Information

Brett Kaiser (x002548) — Thu, 22 Feb 2007 18:05:05 GMT

I've been asked to assist (this time BEFORE Project initiation for a change) in the developmennt of a new application. The Business Liason/Tech group that is doing this has been collecting requirements (basically reviewing an EXCEL Spreadsheet on steriods) and is coming up with a data model. We will be doing a model review, but I was able to guide them in how to record the information so I can leverage the data to generate the tables. I've done this several times already, and they are still fine tuning the table defintion. I then take that document (Excel, again) and generate the DDL and drop it into ERWin (I'll post that code later).

Now they are curious on how to define relationships, Primary Keys and Alternate keys. I decided to figure out the best way to guide them in documenting this so I could leverage that as well. So I came up with the following. Just ask your Business Liason group to record the information in the following form, then just use the code below and generate all of your code.

Basically, you need a spreadsheet with the follwoing information

Parent Table, Key Column, Child Table, Key Type, Key Order and Key Sequence.

Parent Table as it implies is the Parent in a relationship. For Alterante Keys, Primary Keys it is the table that key info is being generated for. Key Column is the Column that will be used in the key. Child Table as is implied the child of a relationship. It is only used for a Foreign Key type. Key Type is P for Primary, F for Foreign Key amd A for an Alternate Key. Key Order defines the Order of Columns in a key, and finally Key Sequence defines the Order in which Foreign or Alternate keys are created. It is also used as part of the index or constraint.

I have been on the wrong end of documentation gone bad too many times, and there is immense push back when you tell them you can't use it...OK, you can use, you have to read it, then retype everything that they already typed.

I hope you find this useful.

CREATE TABLE myTable99 (
Parent sysname
, keyColumn sysname
, Child sysname
, keyType char(1)
, keyOrder int
, keySequence int)
GO

INSERT INTO myTable99(Parent, keyColumn, Child, keyType, keyOrder, keySequence)
SELECT 'myEmployee', 'EMPL_ID', 'myDirectory', 'F', 1, 1 UNION ALL
SELECT 'myEmployee', 'EMPL_ID', ''           , 'P', 1, 1 UNION ALL
SELECT 'myEmployee', 'SSN'    , ''           , 'A', 1, 1 UNION ALL
SELECT 'myDirectory', 'PHONE' , ''           , 'P', 1, 1 UNION ALL
SELECT 'myDirectory', 'EMPL_ID', ''           , 'P', 2, 1
GO

SELECT * FROM myTable99
GO

CREATE TABLE myEmployee(EMPL_ID char(12) NOT NULL, SSN char(9), EMP_NAME varchar(50))
CREATE TABLE myDirectory(PHONE char(10) NOT NULL, EMPL_ID char(12) NOT NULL)
GO

SELECT SQL FROM (
SELECT DISTINCT 'ALTER TABLE ' + Parent
+ ' WITH NOCHECK ADD CONSTRAINT '
+ Parent + '_PK PRIMARY KEY (' AS SQL
, Parent, 1 AS SQL_GROUP, 1 AS keyOrder
FROM myTable99
WHERE keyType = 'P'
UNION ALL
SELECT '   '+keyColumn AS SQL
, Parent, 2 AS SQL_GROUP, keyOrder
FROM myTable99
WHERE keyOrder = 1
   AND keyType = 'P'
UNION ALL
SELECT ' , '+keyColumn AS SQL
, Parent, 3 AS SQL_GROUP, keyOrder
FROM myTable99
WHERE keyOrder <> 1
   AND keyType = 'P'
UNION ALL
SELECT DISTINCT ' )' AS SQL
, Parent, 4 AS SQL_GROUP, 1 AS keyOrder
FROM myTable99
WHERE keyType = 'P'
UNION ALL
SELECT DISTINCT 'GO' AS SQL
, Parent, 5 AS SQL_GROUP, 1 AS keyOrder
FROM myTable99
WHERE keyType = 'P') AS XXX
ORDER BY Parent, SQL_GROUP, keyOrder

/* Produces

ALTER TABLE myDirectory WITH NOCHECK ADD CONSTRAINT myDirectory_PK PRIMARY KEY (
PHONE
, EMPL_ID
)
GO
ALTER TABLE myEmployee WITH NOCHECK ADD CONSTRAINT myEmployee_PK PRIMARY KEY (
EMPL_ID
)
GO

SELECT SQL FROM (
SELECT DISTINCT 'CREATE UNIQUE INDEX '
+ Parent + '_AK'+CONVERT(varchar(3),keySequence)+' ON ' + Parent + ' ( ' AS SQL
, Parent, 1 AS SQL_GROUP, 1 AS keyOrder
FROM myTable99
WHERE keyType = 'A'
UNION ALL
SELECT '   '+keyColumn AS SQL
, Parent, 2 AS SQL_GROUP, keyOrder
FROM myTable99
WHERE keyOrder = 1
   AND keyType = 'A'
UNION ALL
SELECT ' , '+keyColumn AS SQL
, Parent, 3 AS SQL_GROUP, keyOrder
FROM myTable99
WHERE keyOrder <> 1
   AND keyType = 'A'
UNION ALL
SELECT DISTINCT ' )' AS SQL
, Parent, 4 AS SQL_GROUP, 1 AS keyOrder
FROM myTable99
WHERE keyType = 'A'
UNION ALL
SELECT DISTINCT 'GO' AS SQL
, Parent, 5 AS SQL_GROUP, 1 AS keyOrder
FROM myTable99
WHERE keyType = 'A') AS XXX
ORDER BY Parent, SQL_GROUP, keyOrder

/* Produces

CREATE UNIQUE INDEX myEmployee_AK1 ON myEmployee (
SSN
)
GO

SELECT SQL FROM (
SELECT DISTINCT 'ALTER TABLE ' + Child + ' ADD FOREIGN KEY (' AS SQL
, Parent, 1 AS SQL_GROUP, 1 AS keyOrder
FROM myTable99
WHERE keyType = 'F'
UNION ALL
SELECT '   '+keyColumn AS SQL
, Parent, 2 AS SQL_GROUP, keyOrder
FROM myTable99
WHERE keyOrder = 1
   AND keyType = 'F'
UNION ALL
SELECT ' , '+keyColumn AS SQL
, Parent, 3 AS SQL_GROUP, keyOrder
FROM myTable99
WHERE keyOrder <> 1
   AND keyType = 'F'
UNION ALL
SELECT DISTINCT ' )' AS SQL
, Parent, 4 AS SQL_GROUP, 1 AS keyOrder
FROM myTable99
WHERE keyType = 'F'
UNION ALL
SELECT DISTINCT 'REFERENCES ' + Parent + ' (' AS SQL
, Parent, 5 AS SQL_GROUP, 1 AS keyOrder
FROM myTable99
WHERE keyType = 'F'
UNION ALL
SELECT '   '+keyColumn AS SQL
, Parent, 6 AS SQL_GROUP, keyOrder
FROM myTable99
WHERE keyOrder = 1
   AND keyType = 'F'
UNION ALL
SELECT ' , '+keyColumn AS SQL
, Parent, 7 AS SQL_GROUP, keyOrder
FROM myTable99
WHERE keyOrder <> 1
   AND keyType = 'F'
UNION ALL
SELECT DISTINCT ' )' AS SQL
, Parent, 8 AS SQL_GROUP, 1 AS keyOrder
FROM myTable99
WHERE keyType = 'F'
UNION ALL
SELECT DISTINCT 'GO' AS SQL
, Parent, 9 AS SQL_GROUP, 1 AS keyOrder
FROM myTable99
WHERE keyType = 'F') AS XXX
ORDER BY Parent, SQL_GROUP, keyOrder

ALTER TABLE myDirectory ADD FOREIGN KEY (
EMPL_ID
)
REFERENCES myEmployee (
EMPL_ID
)
GO

DROP TABLE myTable99
DROP TABLE myEmployee, myDirectory

What'dya mean I can't TRUNCATE Tables that have RI?

Brett Kaiser (x002548) — Tue, 20 Feb 2007 16:58:20 GMT

So, we've gotten into the business of sanitizing or scrambling sensitive production data for development environments. This is, for the most part, was the direction to do this for mainframe flat files. Now comes along distributed environments, mostly 3rd party vendor applications on sql server. You should see some of the twisted things these apps do. Using reserved words as column names, creating tables with the same name but different owners..the list is long.

In any case, when we told the people who support this mess that we would need fixed width flat files, they were.."but...but...we have over 1,000 tables". Long story short, I wrote a bunch of sprocs to automate alot of the steps needed to get the data out and put it back so they could have a sanitized environment.

One of the issues we ran across was how to handle all of this with RI. Now I guess I could have had them set up a data dictionary that show the relationships, but I choose a lazy way out. I used the catalogs to copy all the RI to a Work table, DROP All of the RI (This is done with another sproc I have yet o post) so that it could be "replayed" after I was done with the TRUNCATES and the bcp in's. Here's a sproc that will log all the RI for any database. Be forewarned...it takes dynamic sql to a whole other level. If anyone has anything simpler, I'd like to see it, but this works fine.

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[isp_Gen_FK_code]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[isp_Gen_FK_code]
GO

CREATE procedure isp_Gen_FK_code
@dbname varchar(255)

AS
/*

EXEC isp_Gen_FK_code
@dbname = 'OHM_Prod'

SELECT * FROM FK_create_code

SET NOCOUNT ON
DECLARE @tablename nvarchar(128), @column nvarchar(128), @schema nvarchar(128), @constraint nvarchar(128)
DECLARE @fktable nvarchar(128), @fkconstraint nvarchar(128), @onupdate varchar(9), @ondelete varchar(9)
DECLARE @comma char(1), @createsql nvarchar(4000), @dropsql nvarchar(4000), @truncatesql nvarchar(4000)
DECLARE @DYSQL nvarchar(4000),@ColList sysname, @ColList2 sysname

DECLARE @error_out int, @Result_Count int, @Error_Message varchar(255), @Error_Type int, @Error_Loc int, @rc int

SELECT @rc = 0
CREATE TABLE ##Key_Column_usage(Constraint_catalog sysname,CONSTRAINT_SCHEMA sysname, CONSTRAINT_NAME varchar(300)
,TABLE_CATALOG sysname, TABLE_SCHEMA sysname, TABLE_NAME sysname, COLUMN_NAME sysname
,ORDINAL_POSITION int)

SET @DYSQL = 'use '+@dbname+'
insert into ##Key_Column_usage
select '''+@dbname+'''    as CONSTRAINT_CATALOG
,user_name(c_obj.uid) as CONSTRAINT_SCHEMA
,c_obj.name    as CONSTRAINT_NAME
,'''+@dbname+'''    as TABLE_CATALOG
,user_name(t_obj.uid) as TABLE_SCHEMA
,t_obj.name    as TABLE_NAME
,col.name    as COLUMN_NAME
,case col.colid
  when ref.fkey1 then 1
  when ref.fkey2 then 2
  when ref.fkey3 then 3
  when ref.fkey4 then 4
  when ref.fkey5 then 5
  when ref.fkey6 then 6
  when ref.fkey7 then 7
  when ref.fkey8 then 8
  when ref.fkey9 then 9
  when ref.fkey10 then 10
  when ref.fkey11 then 11
  when ref.fkey12 then 12
  when ref.fkey13 then 13
  when ref.fkey14 then 14
  when ref.fkey15 then 15
  when ref.fkey16 then 16
end      as ORDINAL_POSITION
from
'+@dbname+'.dbo.sysobjects c_obj
,'+@dbname+'.dbo.sysobjects t_obj
,'+@dbname+'.dbo.syscolumns col
,'+@dbname+'.dbo.sysreferences ref
where
permissions(t_obj.id) != 0
and c_obj.xtype in ('+'''F'''+ ')
and t_obj.id = c_obj.parent_obj
and t_obj.id = col.id
and col.colid   in
(ref.fkey1,ref.fkey2,ref.fkey3,ref.fkey4,ref.fkey5,ref.fkey6,
ref.fkey7,ref.fkey8,ref.fkey9,ref.fkey10,ref.fkey11,ref.fkey12,
ref.fkey13,ref.fkey14,ref.fkey15,ref.fkey16)
and c_obj.id = ref.constid
union
select
'''+@dbname+'''    as CONSTRAINT_CATALOG
,user_name(c_obj.uid) as CONSTRAINT_SCHEMA
,i.name     as CONSTRAINT_NAME
,'''+@dbname+'''    as TABLE_CATALOG
,user_name(t_obj.uid) as TABLE_SCHEMA
,t_obj.name    as TABLE_NAME
,col.name    as COLUMN_NAME
,v.number    as ORDINAL_POSITION
from
'+@dbname+'.dbo.sysobjects  c_obj
,'+@dbname+'.dbo.sysobjects  t_obj
,'+@dbname+'.dbo.syscolumns  col
,master.dbo.spt_values v
,'+@dbname+'.dbo.sysindexes  i
where
permissions(t_obj.id) != 0
and c_obj.xtype in ('+'''UQ'''+' ,'+'''PK'''+')
and t_obj.id = c_obj.parent_obj
and t_obj.xtype = '+'''U'''+'
and t_obj.id = col.id
and col.name = index_col(user_name(t_obj.uid)+'+'''.'''+'+t_obj.name,i.indid,v.number)
and t_obj.id = i.id
and c_obj.name = i.name
and v.number > 0
  and v.number <= i.keycnt
  and v.type = '+'''P'''+''
execute sp_executesql @DYSQL

TRUNCATE TABLE FK_create_code
SET @DYSQL = 'declare cstrts cursor fast_forward read_only for
SELECT DISTINCT
   c.[TABLE_SCHEMA]
, c.[TABLE_NAME]
, u.CONSTRAINT_NAME
FROM   ' +@dbname+'.[INFORMATION_SCHEMA].[COLUMNS] c
JOIN   ##Key_Column_usage u
    ON    c.[TABLE_NAME]      = u.[TABLE_NAME]
   AND   c.[TABlE_SCHEMA]    = u.[TABLE_SCHEMA]
   AND    c.[COLUMN_NAME]     = u.[COLUMN_NAME]
JOIN   ' +@dbname+'.[INFORMATION_SCHEMA].[table_constraints] t
    ON    u.[CONSTRAINT_NAME] = t.[CONSTRAINT_NAME]
WHERE    t.[CONSTRAINT_TYPE] = ' + '''FOREIGN KEY'''

execute sp_executesql @DYSQL
Select @error_out = @@error
If @error_out <> 0
  BEGIN
   SELECT @Error_Loc = 1, @Error_Type = 50001, @rc = -1
   GOTO isp_Gen_FK_code_Error
  END

OPEN cstrts

fetch next from cstrts
into @schema, @tablename, @constraint

while @@fetch_status = 0
begin

SET @DYSQL = 'DECLARE @cr nchar(2), @go nvarchar(8)
SET @cr = nchar(13)+nchar(10)
SET @go = @cr + '+'''GO'''+' + @cr

SELECT DISTINCT
   @fktable = u2.[TABLE_NAME]
, @fkconstraint = r.[UNIQUE_CONSTRAINT_NAME]
, @onupdate = r.[UPDATE_RULE]
, @ondelete = r.[DELETE_RULE]
--, @column = u.[COLUMN_NAME]
FROM ' +@dbname+'.[INFORMATION_SCHEMA].[REFERENTIAL_CONSTRAINTS] r
JOIN ##Key_Column_usage u2
    ON r.[UNIQUE_CONSTRAINT_NAME] = u2.[CONSTRAINT_NAME]
JOIN ##Key_Column_usage u
    ON u.[CONSTRAINT_NAME] = r.[CONSTRAINT_NAME]
WHERE r.[CONSTRAINT_NAME] = @constraint

SELECT @ColList = Null

select @ColList = COALESCE(@ColList + '+ ''','''+ ', '+'''''' +') + '
   +'''['''+' +CAST(COLUMN_NAME AS sysname)+'+''']'''+'
FROM (SELECT DISTINCT TOP 100 u.COLUMN_NAME, u.[ORDINAL_POSITION]
        FROM ' +@dbname+'.[INFORMATION_SCHEMA].[REFERENTIAL_CONSTRAINTS] r
        JOIN ##Key_Column_usage u2
          ON r.[UNIQUE_CONSTRAINT_NAME] = u2.[CONSTRAINT_NAME]
        JOIN ##Key_Column_usage u
          ON u.[CONSTRAINT_NAME] = r.[CONSTRAINT_NAME]
       WHERE r.[CONSTRAINT_NAME] = @constraint
    ORDER BY u.[ORDINAL_POSITION]
) AS XXX
set @createsql =
'+ '''ALTER TABLE ['+@dbname+'].[''' +'
+ @schema
+ '+'''].['''+'
+ @tablename
+ '+'''] ADD CONSTRAINT ['''+'
+ @constraint
+ '+'''] '''+'
+ @cr
+ '+'''FOREIGN KEY ('''+'
+ @colList
+ '+''') REFERENCES ['+@dbname+'].[''' +'
+ @schema
+ '+'''].['''+'
+ @fktable
+ '+'''] ('''+'

CREATE TABLE #Ver(Dbversion varchar(2000))
INSERT INTO #Ver
SELECT @@Version

SELECT @ColList2 = Null

select @ColList2 = COALESCE(@ColList2 + '+ ''','''+ ', '+'''''' +') + '
   +'''['''+' +CAST(c.COLUMN_NAME AS sysname)+'+''']'''+'
    FROM ' +@dbname+'.[INFORMATION_SCHEMA].[COLUMNS] c
    JOIN ##Key_Column_usage u
      ON c.[TABLE_NAME] = u.[TABLE_NAME]
     AND c.[COLUMN_NAME] = u.[COLUMN_NAME]
   WHERE u.[CONSTRAINT_NAME] = @fkconstraint
     AND u.[CONSTRAINT_SCHEMA] = c.[TABLE_SCHEMA]
ORDER BY u.[ORDINAL_POSITION]

set @createsql = @createsql + @colList2

IF EXISTS(SELECT * FROM #Ver where dbversion like '+ '''%8.00%'''+')
BEGIN

set @createsql = @createsql + '+''') ON DELETE '''+'
+ @ondelete
+ '+''' ON UPDATE '''+'
+ @onupdate
END
ELSE
BEGIN
set @createsql = @createsql + '+''')'''+'
END

INSERT INTO FK_Create_code (FK_Code)
VALUES (@createsql)

--print @createsql'

execute sp_executesql @DYSQL,N' @tablename nvarchar(128), @column nvarchar(128), @schema nvarchar(128), @constraint nvarchar(128)
,@fktable nvarchar(128), @fkconstraint nvarchar(128), @onupdate varchar(9), @ondelete varchar(9)
, @comma char(1), @createsql nvarchar(4000), @dropsql nvarchar(4000), @truncatesql nvarchar(4000),@ColList nvarchar(300)
, @colList2 nvarchar(300)'
,@tablename,@column,@schema, @constraint, @fktable, @fkconstraint , @onupdate, @ondelete, @comma, @createsql
, @dropsql, @truncatesql, @ColList, @colList2

Select @error_out = @@error
If @error_out <> 0
  BEGIN
   SELECT @Error_Loc = 1, @Error_Type = 50001, @rc = -1
   GOTO isp_Gen_FK_code_Error
  END

fetch next from cstrts
into @schema, @tablename, @constraint

end

isp_Gen_FK_code_Exit:
close cstrts
deallocate cstrts
DROP TABLE ##KEY_column_usage
RETURN @rc
SET NOCOUNT OFF

isp_Gen_FK_code_Error:

RAISERROR @Error_Type @Error_Message

GOTO isp_Gen_FK_code_Exit

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET Versus SELECT (Or, Who Really Cares Anyway)

Brett Kaiser (x002548) — Mon, 12 Feb 2007 19:36:02 GMT

EDIT: As Tara points out:

Vyas did this test quite some time ago: http://vyaskn.tripod.com/differences_between_set_and_select.htm Either I never read it, or I forgot I read it. Well hopefully I pulled some different points together differently here than Vyas did, and at the very least, I hope I made my feeling clear about a program that has to loop over 2 million times. Thanks for the heads up Tara...

OK, this always comes up from time to time, and it always seem that people are both sides of the fence. "SET is faster because....", "No, SELECT is faster because". Well there should be no debate about it, yet I've failed to find a definitive explanation of what the true story behind this is. In any case I've wanted to look into this.

In this thread the question comes up again. In that thread, Peter posts a link to a SQL Server Magazinee Article that discusses this topic. While it was a good read, I had a hard time buying it. The author (who is listed as "Reader") posted that in using SET, it is optimized and when a single value is set that this is more effecient. They then go on to say that after 1 million iterations with multiple value stes, SELECT was 59% more effecient for each operation. Now the fact that they are doing checks between each assignment seems to cause a potential unexpected interference by SQL server to the outcome of the results. Now, in above article, the first line states "Loops are fairly common in SQL Server stored procedures. ", which in itself is sort of a red herring, since if you are coding that way, I would sugest that you step back and rethink your process. If you can't find a set based solution for 99% for what you have to do, then drop me a line, or head on over to SQL Team.

So I set forth for my own test. I did my tests with an undisturbed loop where dattime values were grabbed before and after the loops of pure sets. I did for 1,000, 10,000, 1,000,000 and 10,000,000. Looking Kalen's book (Inside SQL Server 2000), she has a chapter th differences between the two, but nothing about performance. I'll need to google around some more, however, there must be an explanation about the internals. I did the test for Multiple variable assignments, and another set for single variable assignments. For the 1 million iteration (and it's really 2 million assignments) I got the following:

SELECT_MS_Multiple SET_MS_Multiple
------------------ ---------------
43470 202963

SELECT_MS_Single   SET_MS_Single
----------------                    -------------
42453                             45746

Now in both Cases, SELECT wins, in the case of Multiple assignments, SELECT seems to blow SET's doors off. Now, I need to reiterate this again. If you are finding that you have a process that needs to loop 1 million times, you either are backed into a corner due to previous developement that can't be changed, you've run into the 1% of the time that you have to, or you have a flawed application design.

If Anyone sees anyuthing wrong with this test, or if anyone has any comments I would look forward to it. With all that said, I use SELECT almost exclusively. Here's the code:

DECLARE   @SET1 int,      @SELECT1 int,      @SET2 int,           @SELECT2 int
DECLARE   @SET3 int,      @SELECT3 int,      @SET4 char,          @SELECT4 char
DECLARE   @SET5 char,     @SELECT5 char,     @SET6 char,          @SELECT6 char
DECLARE   @SET7 datetime, @SELECT7 datetime, @SET8 datetime,      @SELECT8 datetime
DECLARE   @SET9 datetime, @SELECT9 datetime, @SETA varchar(8000), @SELECTA varchar(8000)

DECLARE @x int, @s1 datetime, @s2 datetime, @e1 datetime, @e2 datetime, @c int
DECLARE @s3 datetime, @s4 datetime, @e3 datetime, @e4 datetime

SELECT @x = 1, @s1 = getDate(), @c = 1000000

WHILE @x < @c
BEGIN
SELECT    @SELECT1 = 1
  , @SELECT2 = 2
  , @SELECT3 = 3
  , @SELECT4 = 'a'
  , @SELECT5 = 'b'
  , @SELECT6 = 'c'
  , @SELECT7 = '2001-09-11'
  , @SELECT8 = GetDate()
  , @SELECT9 = '1999-12-31'
  , @SELECTA = 'This is a test of the emergency Broadcationg System. This is only a test'

SELECT    @SELECT1 = 0
  , @SELECT2 = 0
  , @SELECT3 = 0
  , @SELECT4 = ''
  , @SELECT5 = ''
  , @SELECT6 = ''
  , @SELECT7 = ''
  , @SELECT8 = 0
  , @SELECT9 = 0
  , @SELECTA = ''

SET @x = @x + 1
END

SELECT @x = 1, @s2 = getDate(), @e1 = getDate()

WHILE @x < @c
BEGIN
SET       @SELECT1 = 1
SET   @SELECT2 = 2
SET    @SELECT3 = 3
SET   @SELECT4 = 'a'
SET   @SELECT5 = 'b'
SET   @SELECT6 = 'c'
SET   @SELECT7 = '2001-09-11'
SET   @SELECT8 = GetDate()
SET   @SELECT9 = '1999-12-31'
SET    @SELECTA = 'This is a test of the emergency Broadcationg System. This is only a test'

SET       @SELECT1 = 0
SET   @SELECT2 = 0
SET    @SELECT3 = 0
SET   @SELECT4 = ''
SET   @SELECT5 = ''
SET   @SELECT6 = ''
SET   @SELECT7 = 0
SET   @SELECT8 = 0
SET   @SELECT9 = 0
SET    @SELECTA = ''

SET @x = @x + 1
END

SELECT @e2 = getDate()

SELECT @x = 1, @s3 = getDate()

WHILE @x < @c
BEGIN
SELECT @SELECT1 = 1

SELECT @SELECT1 = 0

SET @x = @x + 1
END

SELECT @x = 1, @s4 = getDate(), @e3 = getDate()

WHILE @x < @c
BEGIN
SET @SELECT1 = 1

SET @SELECT1 = 0

SET @x = @x + 1
END

SELECT @e4 = getDate()

SELECT DATEDIFF(ms,@s1,@e1) AS SELECT_MS_Multiple, DATEDIFF(ms,@s2,@e2) AS SET_MS_Multiple
SELECT DATEDIFF(ms,@s3,@e3) AS SELECT_MS_Single, DATEDIFF(ms,@s4,@e4) AS SET_MS_Single

3rd and 45? Drop back and Punt? Nah, Generate INSERTS

Brett Kaiser (x002548) — Thu, 08 Feb 2007 19:39:31 GMT

EDIT 2007/09/06: I've modified the sproc to change the dates to be formatted to 121 and stripped out all trailing spaces for char's

So, we don't have DBArtisan, but I am very happy for my copy of ERWin. Don't know what I'd do with out it.

So we have some requirements where they want to create insert statements to load a production table. I said, why not just bcp the data out in native format and create an osql script for the production DBA's to insert the data, or a sproc perhaps.

There are many better ways in my opinion, but I do like a challenge, so I wrote the following. It's not a very mature sproc...no error handling, doesn't handle images or text (how would you anyway for inserts?), ect

Just supply the table_name to the sproc

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[isp_Generate_Inserts]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[isp_Generate_Inserts]
GO

CREATE PROC isp_Generate_Inserts
@TABLE_NAME sysname
AS

SET NOCOUNT ON

/*
EXEC isp_Generate_Inserts 'BusinessGroup'
EXEC isp_Generate_Inserts 'MEPType'
EXEC isp_Generate_Inserts 'Person'
EXEC isp_Generate_Inserts 'Profile'
EXEC isp_Generate_Inserts 'Status'
EXEC isp_Generate_Inserts 'SubBusinessGroup'
EXEC isp_Generate_Inserts 'XREF'
EXEC isp_Generate_Inserts 'Operator'
EXEC isp_Generate_Inserts 'FORMREF'
EXEC isp_Generate_Inserts 'MEPTERRITORY'
EXEC isp_Generate_Inserts 'MEPTICKLERSTATUS'
*/

DECLARE @INSERT varchar(8000), @COLLIST varchar(8000)
--, @TABLE_NAME sysname
, @SELECT varchar(8000), @cmd varchar(8000), @x int

SELECT @COLLIST = COALESCE(@COLLIST + ', ','') + COLUMN_NAME
FROM INFORMATION_SCHEMA.Columns
WHERE TABLE_NAME = @TABLE_NAME
ORDER BY ORDINAL_POSITION

SELECT @INSERT = 'INSERT INTO ' + @TABLE_NAME + '('+ @COLLIST + ')'

-- SELECT @INSERT

SELECT @SELECT = COALESCE(@SELECT + '+'',''+ ','') +
           CASE WHEN DATA_TYPE
IN ('datetime','smalldatetime')
THEN + ''''+ +''''+''''+''''+'+' + 'COALESCE(CONVERT(varchar(25),' + COLUMN_NAME + ',121),'''')' + '+' + ''''+''''+''''+''''
WHEN DATA_TYPE
NOT IN ('int','bigint','smallint','tinyint','deciaml','numeric','money')
THEN + ''''+ ''''+''''+''''+'+COALESCE(REPLACE(RTRIM(' + COLUMN_NAME + ')' + ','''''''','''''''''''')' + ','''')+' + ''''+''''+''''+''''
ELSE + 'COALESCE(RTRIM(CONVERT(varchar(25),' + COLUMN_NAME + ')),'''''''''''')'
    END
    FROM INFORMATION_SCHEMA.Columns
   WHERE TABLE_NAME = @TABLE_NAME
ORDER BY ORDINAL_POSITION

SELECT @SELECT = 'SELECT ' + @SELECT + ' AS DATA FROM ' + @TABLE_NAME

-- SELECT @SELECT

SET @cmd = 'CREATE VIEW XXX AS ' + @SELECT

EXEC(@cmd)

CREATE TABLE myTable99(RowId int IDENTITY(1,1), Data varchar(8000))

INSERT INTO myTable99(Data) SELECT DATA FROM XXX

SELECT 0 AS RowId, @INSERT AS DATA INTO myTemp99

SET @cmd = 'CREATE VIEW YYY AS '
+'SELECT RowId, DATA FROM myTemp99 '
+'UNION ALL '
+'SELECT RowId, '+ '''' + 'SELECT ' + '''' + ' + DATA+ ' + '''' + ' UNION ALL ' + '''' + ' AS DATA FROM myTable99 WHERE RowId < (SELECT COUNT(*) FROM myTable99)'
+'UNION ALL '
+'SELECT RowId, '+ '''' + 'SELECT ' + '''' + ' + DATA AS DATA FROM myTable99 WHERE RowId = (SELECT COUNT(*) FROM myTable99) '

-- SELECT @cmd

EXEC(@cmd)

SET @cmd = 'bcp "SELECT DATA FROM MEP.dbo.YYY ORDER BY RowId" QUERYOUT D:\MEP\Scripts\INS_'+@TABLE_NAME+'.Dat -T -c -S<servername>'

-- SELECT @cmd

EXEC master..xp_cmdshell @cmd

DROP VIEW XXX, YYY
DROP TABLE myTable99, myTemp99
SET NOCOUNT OFF

EXEC master..xp_cmdshell 'Dir D:\MEP\Scripts\*.*'

Deconstruct Text, word by word, into a single column

Brett Kaiser (x002548) — Wed, 20 Dec 2006 20:27:13 GMT

Why, I still didn't get an answer from the poster in this post.

But with Rudy laying the ground work I came up with the following.

Why anyone would need to do thids, I have no idea.  I added a twist to this version

where it counts the occurances.  The end goal here though was to find the text

row with the most occurances of any 1 word, and order the rows in that order.

Why?  I have no idea.

 
create table somewords
( id integer not null primary key identity
, blah text not null
);
insert into somewords (blah) 
values ('a word that appears maximum number of times in a column')
insert into somewords (blah) 
values ('Is it possible to get words from text columns in a sql server database')
insert into somewords (blah) 
values ('This could solve my problem if reffered column contain only single word')
insert into somewords (blah) 
values ('that''s going to require that you split out every word in the column individually')
insert into somewords (blah) 
values ('the query will definitely not be easy to write')
insert into somewords (blah) 
values ('Please read the sticky at the top of the board')
insert into somewords (blah) 
values ('The physical order of data in a database has no meaning')
GO
CREATE TABLE UniqueWords (
   Word varchar(256)
 , WordId int IDENTITY(1,1)
 , WordCount int DEFAULT(1)
 , Add_Dt datetime DEFAULT (GetDate()))
 GO
CREATE UNIQUE INDEX UnqueWords_PK ON UniqueWords(Word)
GO
CREATE PROC isp_INS_UNIQUE_WORDS
AS
BEGIN
 SET NOCOUNT ON
 DECLARE @Words INT, @Pos INT, @x Int, @str varchar(256)
    , @word varchar(256), @start int, @end int, @exitstart int
 SELECT @Words = 0, @Pos = 1, @x = -1, @Word = '', @start = 1
 DECLARE myCursor CURSOR FOR SELECT Blah FROM SomeWords
 OPEN myCursor
 FETCH NEXT FROM myCursor INTO @str
 WHILE @@FETCH_STATUS = 0
   BEGIN
  WHILE (@x <> 0)
   BEGIN
    SET @x     = CHARINDEX(' ', @str, @Pos)
    IF @x <> 0
      BEGIN 
     SET @end   = @x - @start
     SET @word  = SUBSTRING(@str,@start,@end)
     IF NOT EXISTS (SELECT * FROM UniqueWords WHERE Word = @Word)
      INSERT INTO UniqueWords(Word) SELECT @word
       ELSE
      UPDATE UniqueWords SET WordCount = WordCount + 1 WHERE Word = @Word
     -- SELECT @Word, @@ROWCOUNT,@@ERROR
     -- SELECT @x, @Word, @start, @end, @str
     SET @exitstart = @start + @end + 1
     SET @Pos   = @x + 1
     SET @start = @x + 1
     SET @Words = @Words + 1
      END
    IF @x = 0
      BEGIN
     SET @word  = SUBSTRING(@str,@exitstart,LEN(@str)-@exitstart+1)
     IF NOT EXISTS (SELECT * FROM UniqueWords WHERE Word = @Word)
      INSERT INTO UniqueWords(Word) SELECT @word
       ELSE
      UPDATE UniqueWords SET WordCount = WordCount + 1 WHERE Word = @Word
     -- SELECT @Word, @@ROWCOUNT,@@ERROR
     -- SELECT @x, @Word, @exitstart, LEN(@str)-@exitstart, @str
      END
   END
  FETCH NEXT FROM myCursor INTO @str
  SELECT @Words = 0, @Pos = 1, @x = -1, @Word = '', @start = 1
   END 
   CLOSE myCursor
   DEALLOCATE myCursor
   SET NOCOUNT OFF
 RETURN @Words
END
GO
EXEC isp_INS_UNIQUE_WORDS
GO
SELECT * FROM UniqueWords ORDER BY Word
GO
DROP PROC isp_INS_UNIQUE_WORDS
DROP TABLE UniqueWords, somewords
GO

Generate Triggers for all Tables

Brett Kaiser (x002548) — Wed, 29 Nov 2006 14:49:22 GMT

Well, I did this originally to generate triggers for all tables in a database to audit data changes, and that is simple enough, just move the entire row from the deleted table to a mirrored audit table.

But someone wanted to track activity on tables, so it's a little more simple. Here we create one log table, and any time a dml operation occurs, it is written there.

Enjoy

USE Northwind
GO

CREATE TABLE LOG_TABLE (Add_dttm datetime DEFAULT (GetDate()), TABLE_NAME sysname, Activity char(6))
GO

DECLARE @sql varchar(8000), @TABLE_NAME sysname
SET NOCOUNT ON

SELECT @TABLE_NAME = MIN(TABLE_NAME) FROM INFORMATION_SCHEMA.Tables

WHILE @TABLE_NAME IS NOT NULL
BEGIN
SELECT @sql = 'CREATE TRIGGER [' + @TABLE_NAME + '_Usage_TR] ON [' + @TABLE_NAME +'] '
  + 'FOR INSERT, UPDATE, DELETE AS '
  + 'IF EXISTS (SELECT * FROM inserted) AND NOT EXISTS (SELECT * FROM deleted) '
  + 'INSERT INTO LOG_TABLE (TABLE_NAME,Activity) SELECT ''' + @TABLE_NAME + ''', ''INSERT''' + ' '
  + 'IF EXISTS (SELECT * FROM inserted) AND EXISTS (SELECT * FROM deleted) '
  + 'INSERT INTO LOG_TABLE (TABLE_NAME,Activity) SELECT ''' + @TABLE_NAME + ''', ''UPDATE''' + ' '
  + 'IF NOT EXISTS (SELECT * FROM inserted) AND EXISTS (SELECT * FROM deleted) '
  + 'INSERT INTO LOG_TABLE (TABLE_NAME,Activity) SELECT ''' + @TABLE_NAME + ''', ''DELETE''' + ' GO'
SELECT @sql
EXEC(@sql)
SELECT @TABLE_NAME = MIN(TABLE_NAME) FROM INFORMATION_SCHEMA.Tables WHERE TABLE_NAME > @TABLE_NAME
END
SET NOCOUNT OFF

Hierarchies with a twist...rocks, no salt

Brett Kaiser (x002548) — Mon, 13 Nov 2006 21:36:59 GMT

EDIT: The script has been repaired and paired down.

Basically this "solution" assigns a derived value to each entity...which I called codex for lack of better term. Each Child inherits their Parents Codex node signature. In the code below I show how to add a new position in the tree and how to move an entire branch...not sure what else you would want to do, but if you let me know, I'll take a crack at. It also shows how to "mine" different element of meta data about the tree.

This is a link that launched this discussion

Ever wanted to do set based Hierarchical SQL? We came up with a twist, but you need to create a derived column AND it needs to be maintained, but this is something we implemented in a production system and seems to work very nicely. That was in DB2 and requires more effort, but the SQL Server version allows you to use more complex contructs than DB2. DB2 requires more "leg work" to do the same thing. So it is a "dumbed down" version of SQL. I'm sure the multiple host variable assignments can be consolidated, but I need this as a spec for now.

I offer this...now if someone has thought of this before, please let me know. In any case I built this without any refernce to anything else.

SET NOCOUNT ON

-- Create a table to hold a Position Tree
CREATE TABLE myPositions99 (Manager varchar(50), Employee varchar(50), codex varchar(800))

-- Create Work Tables for all the potential levels
CREATE TABLE Level1 (Level1Code int IDENTITY(1,1), Manager varchar(50), Employee varchar(50), codex varchar(800))
CREATE TABLE Level2 (Level2Code int IDENTITY(1,1), Manager varchar(50), Employee varchar(50), codex varchar(800))
CREATE TABLE Level3 (Level3Code int IDENTITY(1,1), Manager varchar(50), Employee varchar(50), codex varchar(800))
CREATE TABLE Level4 (Level4Code int IDENTITY(1,1), Manager varchar(50), Employee varchar(50), codex varchar(800))
CREATE TABLE Level5 (Level5Code int IDENTITY(1,1), Manager varchar(50), Employee varchar(50), codex varchar(800))
GO

-- Create a view of all the work tables
CREATE VIEW myView99 AS
SELECT * FROM Level1 UNION ALL
SELECT * FROM Level2 UNION ALL
SELECT * FROM Level3 UNION ALL
SELECT * FROM Level4 UNION ALL
SELECT * FROM Level5
GO

INSERT INTO myPositions99(Manager, Employee)
SELECT null  , 'Gerard' UNION ALL
SELECT 'Gerard'  , 'Mike' UNION ALL
SELECT 'Gerard'  , 'Pat' UNION ALL
SELECT 'Gerard'  , 'Dan' UNION ALL
SELECT 'Mike'  , 'Nick' UNION ALL
SELECT 'Mike'  , 'Erinn' UNION ALL
SELECT 'Mike'  , 'Jeanne' UNION ALL
SELECT 'Pat'  , 'Elene' UNION ALL
SELECT 'Pat'  , 'Claudette' UNION ALL
SELECT 'Nick'  , 'Algene' UNION ALL
SELECT 'Nick'  , 'Brett' UNION ALL
SELECT 'Nick'  , 'Lana' UNION ALL
SELECT 'Claudette' , 'Susan' UNION ALL
SELECT 'Claudette' , 'Tom' UNION ALL
SELECT 'Dan'  , 'Bob' UNION ALL
SELECT 'Bob'  , 'Ellen' UNION ALL
SELECT 'Erinn'  , 'Nar' UNION ALL
SELECT 'Erinn'  , 'Gary' UNION ALL
SELECT 'Erinn'  , 'Pete'
GO

SELECT * FROM myPositions99

-- Populate all of the work tables with the employees at their particular levels
-- and assign the codex values

INSERT INTO Level1 (Manager, Employee)
    SELECT l.Manager, l.Employee
      FROM myPositions99 l
     WHERE l.Manager IS NULL

UPDATE Level1 SET Codex = RIGHT(REPLICATE('0',5) + CONVERT(varchar(5),Level1Code),5) FROM Level1

INSERT INTO Level2 (Manager, Employee, Codex)
    SELECT r.Manager, r.Employee, l.Codex
      FROM Level1 l
LEFT JOIN myPositions99 r
ON l.Employee = r.Manager
     WHERE l.Manager IS NULL

UPDATE Level2 SET Codex = COALESCE(Codex,'') + RIGHT(REPLICATE('0',5) + CONVERT(varchar(5),Level2Code),5) FROM Level2

INSERT INTO Level3 (Manager, Employee, Codex)
    SELECT r.Manager, r.Employee, l.Codex
      FROM Level2 l
LEFT JOIN myPositions99 r
ON l.Employee = r.Manager
     WHERE r.Manager IS NOT NULL

UPDATE Level3 SET Codex = COALESCE(Codex,'') + RIGHT(REPLICATE('0',5) + CONVERT(varchar(5),Level3Code),5) FROM Level3

INSERT INTO Level4 (Manager, Employee, Codex)
    SELECT r.Manager, r.Employee, l.Codex
      FROM Level3 l
LEFT JOIN myPositions99 r
ON l.Employee = r.Manager
     WHERE r.Manager IS NOT NULL

UPDATE Level4 SET Codex = COALESCE(Codex,'') + RIGHT(REPLICATE('0',5) + CONVERT(varchar(5),Level4Code),5) FROM Level4

-- We do the final level and check @@ROWCOUNT to show that there are no more levels

INSERT INTO Level5 (Manager, Employee, Codex)
    SELECT r.Manager, r.Employee, l.Codex
      FROM Level4 l
LEFT JOIN myPositions99 r
ON l.Employee = r.Manager
     WHERE r.Manager IS NOT NULL

UPDATE Level5 SET Codex = COALESCE(Codex,'') + RIGHT(REPLICATE('0',5) + CONVERT(varchar(5),Level5Code),5) FROM Level5
GO

-- Using the Viewe (essentially all the work tables) update the position tree
UPDATE P SET CODEX = v.CODEX
FROM myPositions99 P JOIN myView99 v ON p.Employee = v.Employee

-- Show Me Pat's group
    SELECT *
      FROM myPositions99 l
LEFT JOIN myPositions99 r
ON r.Codex LIKE l.codex + '%'
       AND l.Codex <> r.Codex
     WHERE l.Employee = 'Pat'

-- Show me 2 levels down from the top
    SELECT *
      FROM myPositions99 l
LEFT JOIN myPositions99 r
ON r.Codex LIKE l.codex + '%'
       AND l.Codex <> r.Codex
       AND LEN(r.codex)/5 < 4
     WHERE LEN(l.codex)/5 = 1

-- Show me everyones level
SELECT Employee, LEN(codex)/5, Codex AS LevelCode
FROM myPositions99

-- Add a new employee that reports to the top
DECLARE @MNGR_CODEX varchar(800), @NEW_CODEX varchar(800), @CHNG_CODEX varchar(800)

SELECT @MNGR_CODEX = RTRIM(CODEX) FROM myPositions99
WHERE Employee = 'Gerard'

SELECT @NEW_CODEX = @MNGR_CODEX+RIGHT(RTRIM('00000'+CONVERT(varchar(800),(MAX(NEW_CODEX)+1))),5)
FROM (SELECT CONVERT(int,SUBSTRING(CODEX,LEN(@MNGR_CODEX)+1,5)) AS NEW_CODEX
          FROM myPositions99
         WHERE CODEX LIKE @MNGR_CODEX+'%'
           AND LEN(RTRIM(CODEX)) = LEN(@MNGR_CODEX)+5) AS XXX

INSERT INTO myPositions99(Manager, Employee, codex)
SELECT 'Gerard' AS Manager
, 'Mickey' AS Employee
, @NEW_CODEX

-- Show me Gerard's direct reports
SELECT * FROM myPositions99 WHERE Manager = 'Gerard'

-- Now let's move a branch....move Nicky Under Pat

-- What's the tree look like now?
SELECT codex, SPACE(LEN(codex)-5)+Employee FROM myPositions99 ORDER BY codex
GO

-- Get a new codex using Pat's codex info (The Manger) and Nicky's (The Employee)
DECLARE @MNGR_CODEX varchar(800), @NEW_CODEX varchar(800), @CHNG_CODEX varchar(800)
SELECT @MNGR_CODEX = RTRIM(CODEX) FROM myPositions99 WHERE Employee = 'Pat'
SELECT @CHNG_CODEX = RTRIM(CODEX) FROM myPositions99 WHERE Employee = 'Nick'

-- Find the New codex For Nicky, using Pat's Codex Node
SELECT @NEW_CODEX = @MNGR_CODEX+RIGHT(RTRIM('00000'+CONVERT(varchar(800),(MAX(NEW_CODEX)+1))),5)
FROM (SELECT CONVERT(int,SUBSTRING(CODEX,LEN(@MNGR_CODEX)+1,5)) AS NEW_CODEX
          FROM myPositions99
         WHERE CODEX LIKE @MNGR_CODEX+'%'
           AND LEN(RTRIM(CODEX)) = LEN(@MNGR_CODEX)+5) AS XXX

-- Now take the New Codex and change Nicky, and change every underlying Codex Node for the rest of the employees
UPDATE P
SET CODEX = @NEW_CODEX + SUBSTRING(CODEX,LEN(RTRIM(@CHNG_CODEX))+1,800-LEN(RTRIM(@CHNG_CODEX)))
FROM myPositions99 P
WHERE CODEX LIKE @CHNG_CODEX + '%'
GO

-- What's the tree look like now?
SELECT codex, SPACE(LEN(codex)-5)+Employee FROM myPositions99 ORDER BY codex
GO

DROP VIEW myView99
DROP TABLE Level1
DROP TABLE Level2
DROP TABLE Level3
DROP TABLE Level4
DROP TABLE Level5
DROP TABLE myPositions99
GO

How do I find all the tables referenced by Stored Procedures or Functions

Brett Kaiser (x002548) — Fri, 22 Sep 2006 16:32:54 GMT

Like this

SELECT o.name, t.TABLE_NAME, c.text
FROM syscomments c
JOIN sysobjects o
ON c.id = o.id
JOIN INFORMATION_SCHEMA.Tables t
ON c.text LIKE '%'+t.TABLE_NAME+'%'

Stored Procedure Logging

Brett Kaiser (x002548) — Thu, 21 Sep 2006 13:38:09 GMT

Every so often, someone asks, "How do I know who executed a SQL Statement against my database".

Well you can either have SQL Profiler running all the time (which can be very expensive), or you can use Lumingent's Log Explorer.

I have taken a different tack lately.

Any Access to a database I am supporting will be done ONLY Through stored procedures. OK, that's not "lately", but the part I've added is that the developers MUST call the sproc below. What this does is to log every stored procedure call. I now have statistics as to what's being called when, and how long the operation takes. There are several benefits, but the best being that I can see which developers don't have their thinking cap on in dev, and we can proactiveley review these sprocs.

OK, now you say, how do you know that developer is callin the logging sproc, and the short answer is, that in dev, I don't. However, No sprocs get moved to QA without a reivew by me or someone on the DBA team. Once it's in QA, they can't touch the code. After QA Sign off, the that code gets moved to UAT, then up to PROD.

In any case I find it useful, and the execution of the logging is in the microseconds. I guess the downside of a app that gets slammed with millions of hits is that this could add up and affect performance, but shy of that, we have not noticed any impact. In any case, for what it's worth here's the sproc and the table DDL. One other note, I do this on other platforms as well, for example DB2 OS/390...only problem there is the need to monitor the tablespace for this table, since you coul blow out on extents...which is not really a problem on SQL Server...except that you potentially could run out of disk space...In either case, you need to monitor that, and archive the data. Any comments are appreciated.

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[usp_LogProcCalls]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[usp_LogProcCalls]
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

CREATE PROCEDURE [dbo].[usp_LogProcCalls] (
   @SprocName sysname
, @TranStart datetime
, @TranEnd datetime
, @Rows int
, @Err   int
, @Paramters varchar(255)
, @rc   int OUTPUT)
AS
SET NOCOUNT ON

Declare @error int, @RowCount int, @Error_Message varchar(255), @Error_Type int, @Error_Loc int

BEGIN TRAN
DECLARE @LogStart datetime
SELECT @rc = 0, @LogStart = GetDate()
IF (SELECT @@TRANCOUNT) <> 1
   BEGIN
    SELECT @Error_Loc = 1
         , @Error_Message = 'The logging procedure must be executed outside of any transaction. @@TRANSCOUNT='
   + CONVERT(varchar(5),@@TRANCOUNT)
         , @Error_Type = 50002, @rc = -6661
    GOTO usp_LogProcCalls_Error
   END

INSERT INTO Sproc_Log (
[SprocName]
, [TranStart]
, [TranEnd]
, [LogStart]
, [LogEnd]
, [Rows]
, [Err]
, [Paramters])
SELECT
@SprocName
, @TranStart
, @TranEnd
, @LogStart
, GetDate()
, @Rows
, @Err
, @Paramters

Select @RowCount = @@ROWCOUNT, @error = @@error

IF @error <> 0
   BEGIN
    SELECT @Error_Loc = 2, @Error_Type = 50001, @rc = -6662
    GOTO usp_LogProcCalls_Error
   END

IF @RowCount <> 1
   BEGIN
    SELECT @Error_Loc = 3
         , @Error_Message = 'Expected 1 row to be inserted in to the sproc log. Actual Number inserted = '
   + CONVERT(varchar(5),@RowCount)
         , @Error_Type = 50002, @rc = -6663
    GOTO usp_LogProcCalls_Error
   END

COMMIT TRAN

usp_LogProcCalls_Exit:

RETURN

usp_LogProcCalls_Error:

Rollback TRAN

If @Error_Type = 50001
BEGIN

Select @error_message = (Select 'Location: ' + ',"' + RTrim(Convert(char(3),@Error_Loc))
          + ',"' + ' @@ERROR: ' + ',"' + RTrim(Convert(char(6),error))
          + ',"' + ' Severity: ' + ',"' + RTrim(Convert(char(3),severity))
          From master..sysmessages
        Where error = @error)
END

If @Error_Type = 50002

BEGIN
Select @Error_Message = 'Location: ' + ',"' + RTrim(Convert(char(3),@Error_Loc))
+ ',"' + ' Severity: UserLevel '
+ ',"' + ' Message: ' + ',"' + RTrim(@Error_Message)
END

RAISERROR @Error_Type @Error_Message

GOTO usp_LogProcCalls_Exit
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Sproc_Log]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Sproc_Log]
GO

CREATE TABLE [dbo].[Sproc_Log] (
[SprocName] [sysname] NOT NULL ,
[TranStart] [datetime] NOT NULL ,
[TranEnd] [datetime] NOT NULL ,
[SYSTEM_USER] [char] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[LogStart] [datetime] NOT NULL ,
[LogEnd] [datetime] NOT NULL ,
[Rows] [int] NOT NULL ,
[Err] [int] NOT NULL ,
[Paramters] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

Finding Trade and Receipt fallout

Brett Kaiser (x002548) — Wed, 20 Sep 2006 17:31:21 GMT

A director came to me asking if a set based approach could be used to find the fallout for trades and receipts. Even though they handed the work off already. The developer used COBOL to compare the 2 file and did "spin-up" processing to match a trade to a receipt. Only probalem is that is totally arbitrary since the "key" was basicall generic and did not exactly marry the receipt to the trade. So their "fallout" was based on LILO. They considered everything else as a match.

What they really needed to do was identify target population that have "fallout" and address those populations as a whole. Which they have not I don't think..mostly because they believe they have their solutiion. In any case, this, in my own opinion (MOO) is what should have been done.

USE Northwind
GO

CREATE TABLE myTrades99 (myKey int, myDate datetime)
CREATE TABLE myReceipts99 (myKey int, myDate datetime)
GO

INSERT INTO myTrades99(myKey, myDate)
SELECT 1, '1/1/2006' UNION ALL
SELECT 1, '2/1/2006' UNION ALL
SELECT 1, '3/1/2006' UNION ALL
SELECT 2, '1/1/2006' UNION ALL
SELECT 3, '1/1/2006' UNION ALL
SELECT 3, '2/1/2006' UNION ALL
SELECT 3, '3/1/2006' UNION ALL
SELECT 3, '4/1/2006' UNION ALL
SELECT 3, '5/1/2006'
GO

INSERT INTO myReceipts99(myKey, myDate)
SELECT 2, '1/1/2006' UNION ALL
SELECT 3, '1/1/2006' UNION ALL
SELECT 3, '2/1/2006' UNION ALL
SELECT 3, '3/1/2006' UNION ALL
SELECT 3, '4/1/2006' UNION ALL
SELECT 3, '5/1/2006' UNION ALL
SELECT 3, '1/1/2006' UNION ALL
SELECT 3, '2/1/2006' UNION ALL
SELECT 3, '3/1/2006' UNION ALL
SELECT 3, '4/1/2006' UNION ALL
SELECT 3, '4/1/2006' UNION ALL
SELECT 4, '5/1/2006'
GO

   SELECT COALESCE(XXX.myKey, YYY.myKey) as myKey, TradeCount, ReceiptCount
     FROM (
     SELECT 'Trade' AS Source, myKey, COUNT(*) AS TradeCount
       FROM myTrades99
   GROUP BY myKey) AS XXX
FULL JOIN (
     SELECT 'Receipt' AS Source, myKey, COUNT(*) AS ReceiptCount
       FROM myReceipts99
   GROUP BY myKey) AS YYY
       ON XXX.myKey = YYY.myKey
    WHERE COALESCE(ReceiptCount,0) <> COALESCE(TradeCount,0)

-- Returns the fallout population with Set based processing

-- myKey       TradeCount ReceiptCount
-- ----------- ----------- ------------
-- 1           3           NULL
-- 3           5           10
-- 4           NULL        1
--
-- (3 row(s) affected)

SET NOCOUNT OFF
DROP TABLE myTrades99, myReceipts99
GO

sp_depends for DB2

Brett Kaiser (x002548) — Wed, 20 Sep 2006 15:21:55 GMT

Well, there really isn't anything that I know of that is like sp_depends for DB2 z/OS Version 7.2. Hopefully V8 will alot more features...but for Now you have to interogate the catalog. So this is how you do it....

    SELECT DISTINCT NAME,DNAME,BNAME
      FROM SYSIBM.SYSPACKDEP D
INNER JOIN SYSIBM.SYSPACKSTMT S
        ON D.DCOLLID = S.COLLID AND D.DNAME = S.NAME
       AND D.DCONTOKEN = S.CONTOKEN
     WHERE BQUALIFIER = 'AXHRSPDA'
       AND BNAME IN('POSITION_TREE')
ORDER BY NAME,DNAME,BNAME
;

And this new editor for posting is pretty neat...lots of features...like background color...and for anyone not familiar with the mainframe...that's what I get to look at all day..until I get another SQL Server project