SQL Dimention!

Otra forma de ejecución controlada de sentencias sql / Another form of controlled execution of sql statements

2014-08-05T07:22:00.001-07:00

Otra forma de ejecucion controlada de sentencias sql

Another form of controlled execution of sql statements

Cuando debemos ejecutar gran cantidad de sentencias sql desatendido y debemos recolectar los errores

When we run unattended lot of sql statements and we collect errors

set NOCOUNT on

DECLARE @SQLCommand VARCHAR (2000)

DECLARE @NSQLCommand NVARCHAR (2000)

DECLARE @SQLERROR INT

declare @TableSQL table (Orden INT IDENTITY , SQL varchar (4000) )

declare @TableSQLErr table ( ErrorNumber int, ErrorDesc varchar (4000), SQL varchar (4000) )

En esta seccion insertaremos en la variable tabla @TableSQL todos los scripts que debemos ejecutar,

aqui un ejemplo de reindexado de tablas

In this section we will insert into @ table Variable TableSQL all scripts that we run,

here an example of re-indexing of tables

insert into @TableSQL select 'ALTER INDEX ALL ON ' + name + ' REBUILD WITH (FILLFACTOR = 80, SORT_IN_TEMPDB = ON, STATISTICS_NORECOMPUTE = ON);' from sys.objects where type = 'U'

/********************Se ejecutan todas las sentencias generadas previamente*******************/

DECLARE FKPK_cursor CURSOR FOR

select SQL from @TableSQL order by orden

OPEN FKPK_cursor;

FETCH NEXT FROM FKPK_cursor

INTO @SQLCommand

WHILE @@FETCH_STATUS = 0

BEGIN

BEGIN TRY

Set @NSQLCommand = Convert(nvarchar(2000), @SQLCommand)

--print @NSQLCommand

EXEC sp_executesql @statement = @NSQLCommand

END TRY

BEGIN CATCH

set @SQLERROR = @@error

Insert into @TableSQLErr values (@SQLERROR, ERROR_MESSAGE(), @SQLCommand)

END CATCH;

FETCH NEXT FROM FKPK_cursor

INTO @SQLCommand

END

CLOSE FKPK_cursor;

DEALLOCATE FKPK_cursor;

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

set NOCOUNT OFF

Resultado: Todas las operaciones ejecutadas

Result: All transactions executed

Select * from @TableSQL order by orden

Resultado: Todos errores ocurridos

Result: All errors

Select * from @TableSQLErr

One parameter, list of values

2011-12-20T05:59:00.001-08:00

/*
Target:
1) Develop a stored procedure where sending a list of values concatenated for space,
bulk insert all records
2) Develop a procedure that receives a list of occurrences can be concatenated by space
bring the possible values

Objetivo:
1) Desarrollar un procedimiento almacenado donde enviando una lista de valores concatenados por espacio,
inserte de forma masiva todos los registros
2) Desarrollar un procedimiento que enviando una lista de ocurrencias concatenadas por espacio puedan
traer los valores posibles

*/

CREATE FUNCTION dbo.fn_ParametersToTable( @ParametrosIn VARCHAR(max))
RETURNS @ParameterTable TABLE ( ParameterValue varchar(50))
AS
BEGIN
/*
Function that can process a parameter and perform a split of the values returned in a table
Funcion que permite procesar un parametro y realizar un split de los valores devolviendolos en una tabla
*/
DECLARE @ParamItem varchar(50)
WHILE CHARINDEX( ' ', @ParametrosIn ) > 0 BEGIN
SET @ParamItem = LOWER( LTRIM(RTRIM( SUBSTRING( @ParametrosIn , 1, CHARINDEX( ' ', @ParametrosIn ) -1))))
SET @ParametrosIn = SUBSTRING( @ParametrosIn , CHARINDEX( ' ', @ParametrosIn ) + 1, len(@ParametrosIn))
IF NOT EXISTS ( SELECT * FROM @ParameterTable WHERE ParameterValue = @ParamItem)
INSERT @ParameterTable ( ParameterValue ) VALUES (@ParamItem)
END
IF LEN(rtrim(@ParametrosIn) ) != '' BEGIN
SET @ParamItem = LOWER( LTRIM(RTRIM( @ParametrosIn )))
IF NOT EXISTS ( SELECT * FROM @ParameterTable WHERE ParameterValue = @ParamItem)
INSERT @ParameterTable ( ParameterValue ) VALUES (@ParamItem )
END
RETURN
END
GO

/*
Sample table to demonstrate the use
Tabla de muestra para demostrar el uso
*/
CREATE TABLE ExampleTbl (field1 int, field2 varchar(20))

GO

CREATE PROCEDURE ExampleInsertList
@ValueList varchar(max)
AS
DECLARE @field1 INT
DECLARE @field2 VARCHAR(20)
/*
Stored procedure taking a single parameter, perform a stock split and inserted into a table

Procedimiento almacenado tomando un solo parametro, realiza un split de valores e inserta en una tabla
*/

INSERT INTO
ExampleTbl
(
field1 ,
field2
)
SELECT
CONVERT(INT, ParameterValue) AS field1,
'Value ' + ParameterValue AS field2
FROM
dbo.fn_ParametersToTable(@ValueList )
GO

/*
Run the test where you insert all the values listed in Table ExampleTbl

Ejecutamos la prueba donde se inserta todos los valores de la lista en la tabla ExampleTbl
*/

EXEC ExampleInsertList @ValueList = '1 2 3 4 5 6 7 8 9 10 11 12 232 4545 125454 5645646 465465 4521 321 2138574 54'

GO

CREATE PROCEDURE ExampleQueryList
@ParameterList VARCHAR(MAX)
AS

/*

Consultation where a list of parameters is the conditional search

Consulta donde una lista de parametros es el condicional de busqueda

*/
SELECT ExampleTbl.* FROM
ExampleTbl
WHERE
field1
IN (
SELECT
CONVERT(INT, ParameterValue)
FROM
dbo.fn_ParametersToTable(@ParameterList )
)

GO

/*
Query Execution of example with lists

Ejecucion del ejemplo de Query con listas
*/
EXEC ExampleQueryList @ParameterList = '1 12 232'

/*

They now have a circuit with all the suggestions of using the function fn_ParametersToTable if your taste and utility can donate what he deems necessary and will be rewarded in many more post

Ahora tienen un circuito con todas las sugerencias del uso de la funcion fn_ParametersToTable si es de su gusto y utilidad puede donar lo que crea conveniente y serà retribuido en muchos mas post

*/

Database Text to NVARCHAR

2011-06-24T10:47:00.000-07:00

This time the problem we faced was: passed all existing TEXT fields in a database field NVARCHAR (MAX).

EASY, enjoy!

En esta oportunidad el problema a resolver era: pasar todos los campos TEXT existentes en una base de datos en campos NVARCHAR(MAX).

Sensillo, enjoy!

Nota: Este script puede dañar la integridad de la base, cualquier daño que provoque será bajo su responsabilidad .

Note: This script can damage the integrity of the base, causing damage thatwill be your responsibility.

DECLARE @CMD VARCHAR(255)

DECLARE @TABLENAME VARCHAR(255)

DECLARE @COLUMNNAME VARCHAR(255)

--Well here declare a cursor, only that

DECLARE Table_cursor CURSOR FOR

--Make the cmd line to be executed

SELECT

'ALTER TABLE ' + O.NAME + ' ALTER COLUMN ' + C.NAME + ' NVARCHAR(MAX) NULL' AS CMD,

O.NAME AS TABLENAME,

C.NAME AS COLUMNNAME

FROM

SYS.OBJECTS AS O

INNER JOIN

SYS.COLUMNS AS C

ON (C.OBJECT_ID = O.OBJECT_ID)

INNER JOIN

SYS.TYPES AS T

ON( T.SYSTEM_TYPE_ID = C.SYSTEM_TYPE_ID )

WHERE

TYPE = 'U' AND C.SYSTEM_TYPE_ID = 35

--Open the cursor

OPEN Table_cursor

--Fetch the first

FETCH NEXT FROM Table_cursor

--Use the field values in the declared vars

INTO @CMD, @TABLENAME, @COLUMNNAME

--LOOP NOT EOF

WHILE @@FETCH_STATUS = 0

BEGIN

PRINT REPLICATE('=',255)

PRINT 'TABLE:' + @TABLENAME

PRINT 'COLUMN:' + @COLUMNNAME

PRINT ''

PRINT 'EXECUTING => ' + @CMD

EXEC (@CMD) --here execute the command

PRINT 'DONE '

--Move next :P

FETCH NEXT FROM Table_cursor

INTO @CMD, @TABLENAME, @COLUMNNAME

--End of cursor

END

--Close an Deallocate cursor

CLOSE Table_cursor

DEALLOCATE Table_cursor

Print replicate('=',255)

DB Source control

2010-10-07T13:11:00.000-07:00

This post is a example of source control on sql databases, You can modify, extend or cut the functions of the next script, ask and discuss, The author is not responsible of content, only for educational purposes.

/*Create a table where save every change on DB */

CREATE TABLE DEVSQLControl (

[ID] INT IDENTITY(1,1) ,

[Name] VARCHAR(256) ,

[Type] CHAR(2) ,

Create_date DATETIME ,

Modify_date DATETIME ,

DataCheckSum BIGINT ,

stamp TIMESTAMP ,

SnapDate DATETIME

)

Insert in to DEVSQLControl table the new changes, including what I want to seek, you are free to change the list to your wish

INSERT INTO

DEVSQLControl

(

[Name] ,

[type] ,

Create_date ,

Modify_date ,

DataCheckSum ,

SnapDate

)

SELECT

O.name ,

O.type ,

O.create_date ,

O.modify_date ,

BINARY_CHECKSUM(

O.name,

O.object_id,

O.principal_id,

O.schema_id,

O.parent_object_id,

O.type,

O.type_desc,

O.create_date,

O.modify_date,

O.is_ms_shipped,

O.is_published,

O.is_schema_published

) ,

Getdate()

FROM

SYS.OBJECTS AS O

INNER JOIN

DEVSQLControl AS DV

ON(-- 1 = 1

O.name COLLATE DATABASE_DEFAULT = DV.name COLLATE DATABASE_DEFAULT

AND O.type COLLATE DATABASE_DEFAULT = DV.type COLLATE DATABASE_DEFAULT

)

WHERE O.TYPE

IN ( 'U','P','fn', 'C ','PK','UQ','F ', 'D ' )

AND DataCheckSum <> BINARY_CHECKSUM(

O.name,

O.object_id,

O.principal_id,

O.schema_id,

O.parent_object_id,

O.type,

O.type_desc,

O.create_date,

O.modify_date,

O.is_ms_shipped,

O.is_published,

O.is_schema_published

)

GROUP BY

O.name ,

O.type ,

O.create_date ,

O.modify_date ,

BINARY_CHECKSUM(

O.name,

O.object_id,

O.principal_id,

O.schema_id,

O.parent_object_id,

O.type,

O.type_desc,

O.create_date,

O.modify_date,

O.is_ms_shipped,

O.is_published,

O.is_schema_published

)

Audit Script Wizard (SQL Server 2008) II

2010-09-29T07:16:00.000-07:00

After posting my article audit Wizard where you create an additional table for each table to be audited, in this issue propose another solution which is created only once a single table and persist data in a xml field, the differences are many, but you have to discover which are the scope and mainly depends on the culture to use xml.

Best regards

See also: Audit Script Wizard (SQL Server 2008)
http://sqldimention.blogspot.com/2010/09/audit-wizard-script.html

Después de publicar mi articulo de Wizard de auditoria donde se crea una tabla adicional por cada tabla a auditar, en esta entrega propongo otra solución donde se crea una única vez una sola tabla y los datos persisten en un campo xml, las diferencias son muchas, pero hay que evaluar cuales son los alcances y sobre todo depende de la cultura a usar xml.

Saludos cordiales

Ver tambien:Audit Wizard Script (SQL server 2008)
http://sqldimention.blogspot.com/2010/09/audit-wizard-script.html

-- =============================================
-- Author: Skynet Silverstar
-- Create date:
-- Description: Script maker where you conform
-- audit info on a specific SQL table
-- =============================================
-- Creador de script donde conforma
-- Informacion de auditoria de
-- una o mas tablas especificas
-- sobre una unica tabla de auditoria
-- =============================================

DECLARE @object_name VARCHAR(50)
DECLARE @column_name VARCHAR(50)
DECLARE @is_nullable VARCHAR(50)
DECLARE @type VARCHAR(50)
DECLARE @max_length INT
DECLARE @precision INT
DECLARE @scale INT
DECLARE @Audit_name VARCHAR(255)
DECLARE @trigger_name VARCHAR(255)

-- The target table name
-- El nombre de la tabla
SET @object_name = 'factura'

--Here change the name of tables and triggers if you want
--Aqui cambie el nombre de las tablas y disparadore si usted quiere!
SET @Audit_name = 'AUDIT_Table'
SET @trigger_name = 'AUDIT_TR_' + @object_name

IF EXISTS( SELECT * FROM SYS.OBJECTS WHERE NAME IN ( @trigger_name) )
BEGIN
PRINT '---------------------------------------------------------'
PRINT '--Informe de proceso con error ATENCION!'
PRINT '---------------------------------------------------------'
PRINT '--El trigger ' + @trigger_name + ' ya existe, verificar y volver a ejecutar el proceso'
PRINT '--'
PRINT '--Sugerido hacer backup de la tabla de auditoria y base de datos'
PRINT '--copiar el resultado y descomentar los comandos siguientes y ejecutar F5'
PRINT '--DROP TRIGGER ' + @trigger_name
PRINT ''
PRINT '---------------------------------------------------------'
PRINT '--Warning!! proccess info error'
PRINT '---------------------------------------------------------'
PRINT '--Trigger ' + @trigger_name + ' already exists, Verify and run again the script'
PRINT '--'
PRINT '--Make a backup and save the information'
PRINT '--Execute the next lines if you want delete the old schema'
PRINT '--DROP TRIGGER ' + @trigger_name

END ELSE BEGIN
SET NOCOUNT ON

PRINT '-------- Script de creacion de tabla de auditoria ' + @Audit_name+ ' si existe--------'
PRINT '-------- Make the script to make the audit table ' + @Audit_name+ ' IF EXISTs--------'
PRINT '-------- AUTOR: ' + USER_NAME()
PRINT '-------- WORKPLACE: ' + HOST_NAME() + '/' + APP_NAME()

PRINT 'SET ANSI_NULLS ON'
PRINT 'GO'
PRINT ''
PRINT 'SET QUOTED_IDENTIFIER ON'
PRINT 'GO'
PRINT ''
PRINT 'SET ANSI_PADDING ON'
PRINT 'GO'
PRINT ''
PRINT 'IF NOT EXISTS( SELECT * FROM SYS.OBJECTS WHERE NAME IN ( ''' + @Audit_name + ''') )'
PRINT 'BEGIN'
PRINT CHAR(9) + 'CREATE TABLE ' + @Audit_name + ' ('
PRINT CHAR(9) + CHAR(9) + ' [AUDIT_ID] ' + CHAR(9) + ' INT IDENTITY(1,1) ' + CHAR(9) + ','
PRINT CHAR(9) + CHAR(9) + ' [AUDIT_ACTION] ' + CHAR(9) + ' CHAR(1) ' + CHAR(9) + ' ,'
PRINT CHAR(9) + CHAR(9) + ' [AUDIT_SPID] ' + CHAR(9) + ' INT ' + CHAR(9) + ' ,'

PRINT CHAR(9) + CHAR(9) + ' [AUDIT_DATE] ' + CHAR(9) + ' DATETIME ' + CHAR(9) + ' ,'
PRINT CHAR(9) + CHAR(9) + ' [AUDIT_USER] ' + CHAR(9) + ' VARCHAR(50) ' + CHAR(9) + ' ,'
PRINT CHAR(9) + CHAR(9) + ' [AUDIT_APLICATION] ' + CHAR(9) + ' VARCHAR(255) ' + CHAR(9) + ' ,'
PRINT CHAR(9) + CHAR(9) + ' [AUDIT_HOST] ' + CHAR(9) + ' VARCHAR(50) ' + CHAR(9) + ' NOT NULL,'
PRINT CHAR(9) + CHAR(9) + ' [AUDIT_TIMESTAMP] ' + CHAR(9) + ' TIMESTAMP ' + CHAR(9) + ' ,'
PRINT CHAR(9) + CHAR(9) + ' [AUDIT_HASH] ' + CHAR(9) + ' BIGINT ' + CHAR(9) + ' ,'
PRINT CHAR(9) + CHAR(9) + ' [AUDIT_TABLENAME] ' + CHAR(9) + ' VARCHAR(255) ' + CHAR(9) + ' ,'
PRINT CHAR(9) + CHAR(9) + ' [AUDIT_XML_CONTENT] ' + CHAR(9) + ' XML ' + CHAR(9) + ' ,'
PRINT CHAR(9) + ' CONSTRAINT [' + @Audit_name + '_PK] PRIMARY KEY CLUSTERED '
PRINT CHAR(9) + ' ('
PRINT CHAR(9) + ' [AUDIT_ID] ASC'
PRINT CHAR(9) + ' )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]'
PRINT CHAR(9) + ' ) ON [PRIMARY]'
PRINT 'END '
PRINT ' '
PRINT ' GO'
PRINT ' '
PRINT ' SET ANSI_PADDING OFF'
PRINT ' GO'

/
creacion del disparador
Make a trigger

/

PRINT '-------- Script de creacion de triger de auditoria AUDIT_TR_' + @object_name+ '--------'
PRINT '-------- Make a script of Audit trigger named AUDIT_TR_' + @object_name+ '--------'
PRINT '-------- AUTOR: ' + USER_NAME()
PRINT '-------- WORKPLACE: ' + HOST_NAME() + '/' + APP_NAME()

PRINT 'SET ANSI_NULLS ON'
PRINT 'GO'
PRINT ''
PRINT 'SET QUOTED_IDENTIFIER ON'
PRINT 'GO'
PRINT ''
PRINT 'SET ANSI_PADDING ON'
PRINT 'GO'
PRINT ''
PRINT 'CREATE TRIGGER ' + @trigger_name + ' '
PRINT ' ON ' + @object_name + ' '
PRINT ' AFTER INSERT, UPDATE, DELETE'
PRINT ' AS'
PRINT ' BEGIN'

PRINT CHAR(9) + ' -- Verifico que tipo de accion se va a realizar'
PRINT CHAR(9) + ' -- U : almaceno el dato a actualizar'
PRINT CHAR(9) + ' -- D : almaceno el dato a ser eliminado'
PRINT CHAR(9) + ' -- I : almaceno el dato a ser insertado'
PRINT ''
PRINT CHAR(9) + ' -- Verify the type of operatio to do'
PRINT CHAR(9) + ' -- U : Save on update'
PRINT CHAR(9) + ' -- D : Save on delete'
PRINT CHAR(9) + ' -- I : save on insert'

PRINT CHAR(9) + 'DECLARE @ACTION CHAR(1)'
PRINT CHAR(9) + 'DECLARE @XMLDATA XML'
PRINT CHAR(9) + 'DECLARE @HASHDATA BIGINT'

PRINT ''
PRINT CHAR(9) + 'IF EXISTS(SELECT * FROM DELETED) AND EXISTS(SELECT * FROM INSERTED)'
PRINT CHAR(9) + CHAR(9) + 'SET @ACTION = ''U'''
PRINT CHAR(9) + 'ELSE IF EXISTS(SELECT * FROM DELETED) '
PRINT CHAR(9) + CHAR(9) + 'SET @ACTION = ''D'' '
PRINT CHAR(9) + 'ELSE IF EXISTS(SELECT * FROM INSERTED)'
PRINT CHAR(9) + CHAR(9) + 'SET @ACTION = ''I'' '
PRINT ''

DECLARE @FIELDS VARCHAR(8000)
SET @FIELDS = ''
DECLARE Table_cursor CURSOR FOR
SELECT
c.name AS column_name
FROM
sys.objects AS o
INNER JOIN
sys.columns AS c
ON ( o.object_id = c.object_id)
WHERE
o.type = 'U'
AND o.name = @object_name
ORDER BY
o.name ,
column_id
OPEN Table_cursor
FETCH NEXT FROM Table_cursor
INTO @column_name

WHILE @@FETCH_STATUS = 0
BEGIN
SET @FIELDS = @FIELDS + '' + ' [' + @column_name + '] ,'

FETCH NEXT FROM Table_cursor
INTO @column_name
END
CLOSE Table_cursor
DEALLOCATE Table_cursor
SET @FIELDS = substring(@FIELDS , 1 , len(@FIELDS ) - 1)
PRINT CHAR(9) + 'SET NOCOUNT ON'

PRINT CHAR(9) + 'IF (@ACTION IN ( ''I'')) BEGIN'
PRINT CHAR(9) + CHAR(9) + 'SET @XMLDATA = CAST((SELECT ' + @FIELDS + ', BINARY_CHECKSUM(' + @FIELDS + ') AS [AUDIT_HASH] FROM INSERTED FOR XML AUTO, Elements) AS XML)'
PRINT CHAR(9) + CHAR(9) + 'INSERT INTO ' + @Audit_name + ' '
PRINT CHAR(9) + CHAR(9) + '( [AUDIT_ACTION], [AUDIT_DATE], [AUDIT_USER], [AUDIT_APLICATION],[AUDIT_HOST], [AUDIT_HASH], [AUDIT_SPID], [AUDIT_TABLENAME] , [AUDIT_XML_CONTENT]) VALUES'
PRINT CHAR(9) + CHAR(9) + '( @ACTION, GetDate(), USER_NAME(), APP_NAME(), HOST_NAME() , 0 , @@SPID, '
PRINT CHAR(9) + CHAR(9) + '''' + @object_name + ''', @XMLDATA ) '
PRINT CHAR(9) + 'END'
PRINT CHAR(9) + 'ELSE IF (@ACTION IN (''U'', ''D'')) BEGIN'
PRINT CHAR(9) + CHAR(9) + 'SET @XMLDATA = CAST((SELECT ' + @FIELDS + ', BINARY_CHECKSUM(' + @FIELDS + ') AS [AUDIT_HASH] FROM DELETED FOR XML AUTO, Elements) AS XML)'
PRINT CHAR(9) + CHAR(9) + 'INSERT INTO ' + @Audit_name + ' '
PRINT CHAR(9) + CHAR(9) + '( [AUDIT_ACTION], [AUDIT_DATE], [AUDIT_USER], [AUDIT_APLICATION],[AUDIT_HOST], [AUDIT_HASH], [AUDIT_SPID], [AUDIT_TABLENAME] , [AUDIT_XML_CONTENT]) VALUES'
PRINT CHAR(9) + CHAR(9) + '( @ACTION, GetDate(), USER_NAME(), APP_NAME(), HOST_NAME() , 0 , @@SPID, '
PRINT CHAR(9) + CHAR(9) + '''' + @object_name + ''', @XMLDATA ) '
PRINT CHAR(9) + 'END'
PRINT 'END'

PRINT '---------------------------------------------------------'
PRINT '--Informe de proceso'
PRINT '---------------------------------------------------------'
PRINT '--Se generò sobre tabla ' + @object_name + ' el trigger ' + @trigger_name + ' '
PRINT '--'
PRINT '--Copie el contenido de resultado a un archivo. sql'
PRINT '--Nombre: xxx_creacion_auditoria_' + @object_name + '.sql'
PRINT ''
PRINT '---------------------------------------------------------'
PRINT '--Proccess info'
PRINT '---------------------------------------------------------'
PRINT '--With the target table ' + @object_name + ' made the trigger ' + @trigger_name + ' '
PRINT '--'
PRINT '--Copy the result to a new query and save '
PRINT '--Name: xxx_Create_Audit_' + @object_name + '.sql'
END

SIMPLE CURSOR EXAMPLE

2010-09-24T11:17:00.000-07:00

SIMPLE CURSOR EXAMPLE

Generation of a table with nested entities using cursors,

Logical entities are generated in a hierarchical

EJEMPLO SIMPLE DE CURSORES Entities dictionary

Generacion de una tabla con entidades anidades usando cursores,

Se generan entidades logicas en forma jerarquica


--Declare a table
DECLARE @Entionary TABLE  ( 
 [ID]   INT IDENTITY(1,1)  ,
 [Type]   VARCHAR(25)    , 
 [PhisicName]  VARCHAR(255)  ,
 [LogicName]  VARCHAR(255)  ,
 [Parent]  INT   ,
 [Synonym]  INT   ,
 [Help]   xml     
)

-- Var where I take the Identity number of Patern row
DECLARE @RootID   INTEGER  

-- Name of table
DECLARE @object_name VARCHAR(255)

-- Logical name... if you like other name is free to change it
DECLARE @LogicName  VARCHAR(255)

-- Modify Date of  Table, only to show other field
DECLARE @modify_date DATETIME

--Insert the root Row where depends every table name
INSERT INTO 
 @Entionary  (
     [Type]  , 
     [PhisicName] ,  
     [LogicName] ,  
     [Parent] , 
     [Synonym] 
    ) values (  
     'Entity' , 
     'Generic' , 
     'Generic' , 
     NULL  , 
     NULL
    )
    
-- Take the Identity value 
SET @RootID = @@IDENTITY

--Well here declare a cursor, only that
DECLARE Table_cursor CURSOR FOR 
 SELECT 
  name  AS [object_name], 
  modify_date
 FROM 
  sys.objects 
 WHERE 
  type = 'U' 
 ORDER BY 
  name
--Open the cursor 
OPEN Table_cursor
--Fetch the first
FETCH NEXT FROM Table_cursor 
--Use the field values in the declared vars
INTO @object_name , @modify_date
--Loop not eof 
WHILE @@FETCH_STATUS = 0
BEGIN
 --Insert table entity
 SET @LogicName = 'Logic_' + @object_name 
 SET @LogicName = @LogicName + '('+convert( Varchar(10), @modify_date)+')'
 INSERT INTO @Entionary (
     [Type]  , 
     [PhisicName] , 
     [LogicName] , 
     [Parent] , 
     [Synonym]
    ) values (  
     'SubEntity' , 
     @object_name , 
     @LogicName , 
     @RootID , 
     NULL 
    )
 --For each table insert every field
 INSERT INTO @Entionary (
     [Type]  , 
     [PhisicName] , 
     [LogicName] , 
     [Parent] , 
     [Synonym]
    ) 
    select 
     'Field'   ,
     c.name   , 
     'LogicField_' + c.name , 
     @@identity  , 
     NULL  
    FROM 
     sys.columns AS c 
    INNER JOIN 
     sys.objects AS o 
    ON ( 
     o.object_id = c.object_id 
    ) 
    WHERE 
     o.name = @object_name

 --Move next :P
 FETCH NEXT FROM Table_cursor
 INTO  @object_name , @modify_date
--End of cursor
END
--Close an Deallocate cursor 
CLOSE Table_cursor
DEALLOCATE Table_cursor

--I Can't use agregate functions with update from (I Don't know why)... with declared tables, use the temp table
SELECT *  INTO #temp FROM  @Entionary

-- other example for set a xml field, only that
UPDATE #temp SET  [Help] = t.Result
FROM 
 (SELECT ID, CONVERT(XML, '' 
 + CHAR(13) + CHAR(9) + 'MRP'
 + CHAR(13) + CHAR(9) + '23/09/2010'
 + CHAR(13) + CHAR(9) + '' + [LogicName] + ''
 + CHAR(13) + CHAR(9) + '' + [PhisicName] + ''
 + CHAR(13) + CHAR(9) + ''
 + CHAR(13) + CHAR(9) + ''
 + CHAR(13) + ' ' )  AS Result
 FROM #temp ) AS t INNER JOIN #temp ON ( #temp.id = t.id) 

--show the ResultSet
SELECT * FROM #temp

--Drop the temp table
DROP TABLE #temp

Task Calendar example

2010-09-22T12:11:00.001-07:00

This example just want to show different ways to solve specific problems, means that this script contains unnecessary processes.

Best regards

--Declare a new table where I will insert every task

DECLARE @CalendarX TABLE  (
[ID] INT IDENTITY(1,1)  ,
[TaskDate] datetime ,
[TaskName] varchar(255)
)

--Other table where insert every day of month
DECLARE @DaysofMonth TABLE  (
[ID] INT   ,
[TaskDate] datetime
)

SET NOCOUNT ON
--Insert examples task rows
Insert into @CalendarX ([TaskDate], [TaskName]) values ( '2010-10-20', 'Task 1')
Insert into @CalendarX ([TaskDate], [TaskName]) values ( '2010-10-19', 'Task 2')
Insert into @CalendarX ([TaskDate], [TaskName]) values ( '2010-10-15', 'Task 3')
Insert into @CalendarX ([TaskDate], [TaskName]) values ( '2010-10-8', 'Task 4')
Insert into @CalendarX ([TaskDate], [TaskName]) values ( '2010-10-21', 'Task 5')
SET NOCOUNT OFF

DECLARE @daytosee   DATETIME
DECLARE @FirstDayofMonth DATETIME
DECLARE @LastDayofMonth  DATETIME
DECLARE @CountofDays  SMALLINT
DECLARE @CurrentDay   SMALLINT
DECLARE @XMLDATA   XML

--Set each var
SET @CurrentDay   = 1
SET @daytosee   = DATEADD(m,1,GETDATE())
SET @CountofDays  = 0 -- Set to zero
SET @FirstDayofMonth         = CONVERT(DATETIME,CONVERT(VARCHAR(4),DATEPART(yyyy, @daytosee))

    + '/' +  CONVERT(VARCHAR(2),DATEPART(m,@daytosee))

    + '/01' )
SET @LastDayofMonth  = DATEADD(d,-1, dateadd(m, 1, @FirstDayofMonth))
SET @CountofDays  = DATEDIFF(d, @FirstDayofMonth, @LastDayofMonth ) + 1

--Show values


--Run a simple while
WHILE @CurrentDay < @CountofDays BEGIN

INSERT INTO @DaysofMonth

 VALUES (@CurrentDay, DATEADD(d, @CurrentDay -1, @FirstDayofMonth))

 SET @CurrentDay = @CurrentDay + 1

END

--Join the tables

BEGIN

 WITH FinalCalendar AS (SELECT

   ISNULL(cx.ID, 0) AS ID,

   dm.TaskDate ,

   ISNULL(cx.TaskName, '[Empty]') AS TaskName

  FROM

   @DaysofMonth AS dm

  LEFT join

   @CalendarX  AS cx

  ON (dm.TaskDate = cx.TaskDate))

 SELECT @XMLDATA  = CAST((SELECT * FROM FinalCalendar FOR XML AUTO) AS XML)

END

--Show the result

SELECT

 @DayToSee     AS DayToSee     ,

 @FirstDayofMonth  AS FirstDayofMonth  ,

 @LastDayofMonth   AS LastDayofMonth  ,

 @CountofDays    AS CountofDays   ,

 @XMLDATA             AS Calendar

/* Compatible SQL 2008 SQL 2008 compatible */

Resultado XML de un select dinamico

2010-09-21T08:03:00.000-07:00

-Quiere obtener el resultado de un select dinamico? no puede hacerlo? :D y ademas quiere sacar un xml como salida?

-Aqui esta el ejemplo que usted buscaba... (probado en sql server 2008).

Saludos cordiales

- Want to get the result of a dynamic select? can not do? : D and also wants to get an xml as output?

- Here is the example you wanted ... (Tested in SQL Server 2008).

Best regards



--I make a temporal table
--Creo una tabla temporal
CREATE TABLE TEMPORALTBL (
 ID INT,
 DESCRIPTION  varchar(200)
)

--Insert the example rows
--Inserto registros de ejemplo
INSERT INTO TEMPORALTBL VALUES (1,'THIS IS A TEST')
INSERT INTO TEMPORALTBL VALUES (2,'MAY ENGLISH IS POOR')
INSERT INTO TEMPORALTBL VALUES (3,'MAY SPANISH IS THE POOREST')


DECLARE @cmd NVARCHAR(2000)
DECLARE @result XML

--My dynamic select
--Mi select dinamico
SET @cmd = N'select top 100 * from temporaltbl '

--I make a cast to xml
--Hago un cast a xml
SET @cmd = N'select @insideresult= CAST((' +@cmd + ' FOR XML AUTO, Elements ) AS xml) '

--EXECUTE THE SELECT!!!! CAN YOU SEE?
--EJECUTO EL SELECT... CABRON LO PUEDES VER?
EXEC sp_executeSQL @cmd ,N'@insideresult xml output',@insideresult = @result OUTPUT

--Show the XML and command
--Muestro el resultado del xml y el comandojavascript:void(0)
SELECT @cmd  as command, @result as Textxml

--Drop the example table
--Borro la tabla de ejemplo
DROP TABLE temporaltbl
/*
Compatible SQL 2008
SQL 2008 compatible
*/

Audit Wizard Script (SQL server 2008)

2010-09-16T06:13:00.000-07:00

Este es el primer post donde desarrollo lo que realmente puede llegar a interesar, en este caso es un simple hacedor de script que genera tablas de auditoria con su respectivo disparador, como siempre digo, todo se puede mejorar pero partiendo de una primera idea se puede mejorar aun mas, Cualquier consulta o idea no existe problema en compartirla y discutirla.

El siguiente script es para educación solamente, no es responsable de cualquier daño o mal uso que el código o parte de el pueda producir.

Saludos cordiales

This is the first post where I develop what can really get interested, here is a simple script that generates a wizard audit tables with its own release, as I always say, everything can be improved but starting from an initial idea can be improve further, Any question or idea to share it no problem and discuss it.

The following script is for education only, is not responsible for any damage or misuse of the code or part of it can produce

Best regards



-- =============================================
-- Author:  Skynet Silverstar
-- Create date:
-- Description: Script maker where you conform
-- audit info on a specific SQL table
-- =============================================
-- Creador de script donde conforma
--    Informacion de auditoria sobre
-- una tabla especifica
-- =============================================


DECLARE @object_name VARCHAR(50)
DECLARE @column_name VARCHAR(50)
DECLARE @is_nullable VARCHAR(50)
DECLARE @type   VARCHAR(50)
DECLARE @max_length  INT
DECLARE @precision  INT
DECLARE @scale   INT
DECLARE @Audit_name  VARCHAR(255)
DECLARE @trigger_name VARCHAR(255)


-- The target table name
-- El nombre de la tabla
SET @object_name = 'factura'




--Here change the name of tables and triggers if you want
--Aqui cambie el nombre de las tablas y disparadore si usted quiere!
SET @Audit_name  = 'AUDIT_' + @object_name
SET @trigger_name = 'AUDIT_TR_' + @object_name


if exists( select * from sys.objects where name in ( @Audit_name, @trigger_name) )
BEGIN
 PRINT '---------------------------------------------------------'
 PRINT '--Informe de proceso con error ATENCION!'
 PRINT '---------------------------------------------------------'
 PRINT '--La tabla de auditoria ' + @Audit_name + ' ya existe, verificar y volver a ejecutar el proceso'
 PRINT '--El trigger ' + @trigger_name + ' ya existe, verificar y volver a ejecutar el proceso'
 PRINT '--'
 PRINT '--Sugerido hacer backup de la tabla de auditoria y base de datos'
 PRINT '--copiar el resultado y descomentar los comandos siguientes y ejecutar F5'
 PRINT '--DROP TABLE ' + @Audit_name
 PRINT '--DROP TRIGGER ' + @trigger_name
 PRINT ''
 PRINT '---------------------------------------------------------'
 PRINT '--Warning!! proccess info error
 PRINT '---------------------------------------------------------'
 PRINT '--The audit table ' + @Audit_name + ' already exists, Verify and run again the script'
 PRINT '--Trigger ' + @trigger_name + ' already exists, Verify and run again the script'
 PRINT '--'
 PRINT '--Make a backup and save the information'
 PRINT '--Execute the next lines if you want delete the old schema'
 PRINT '--DROP TABLE ' + @Audit_name
 PRINT '--DROP TRIGGER ' + @trigger_name


END ELSE BEGIN
 SET NOCOUNT ON



 PRINT '-------- Script de creacion de tabla de auditoria AUDIT_' + @object_name+ '--------'
 PRINT '-------- Make the script to make the audit table AUDIT_' + @object_name+ '--------'
 PRINT '-------- AUTOR: ' + USER_NAME()
 PRINT '-------- WORKPLACE: ' + HOST_NAME() + '/' + APP_NAME()

 PRINT 'SET ANSI_NULLS ON'
 PRINT 'GO'
 PRINT ''
 PRINT 'SET QUOTED_IDENTIFIER ON'
 PRINT 'GO'
 PRINT ''
 PRINT 'SET ANSI_PADDING ON'
 PRINT 'GO'
 PRINT ''
 PRINT 'CREATE TABLE ' + @Audit_name + ' ('
 PRINT CHAR(9) + ' [AUDIT_ID] ' + CHAR(9) + ' INT IDENTITY(1,1) ' + CHAR(9) + ','
 PRINT CHAR(9) + ' [AUDIT_ACTION] ' + CHAR(9) + ' CHAR(1) ' + CHAR(9) + ' ,'
 DECLARE Table_cursor CURSOR FOR
  SELECT
   o.name AS object_name,
   c.name AS column_name,
   is_nullable = CASE WHEN c.is_nullable <> 0 THEN 'NULL' ELSE 'NOT NULL' END,  
   [type] = '[' + upper(t.name) + '] ' + CASE
    WHEN t.name in ('nvarchar', 'nchar','char','varchar') THEN
    '(' + convert(varchar(4), c.max_length) + ')'
    WHEN t.name in ('float', 'numeric','money','smallmoney','real','decimal') THEN
    '(' + CONVERT(VARCHAR(4), c.precision) + ',' + CONVERT(VARCHAR(4), c.scale) + ')'
    ELSE ''

   END,
   c.max_length ,
   c.precision ,
   c.scale
  FROM
   sys.objects AS o
  INNER JOIN
   sys.columns AS c
  ON ( o.object_id = c.object_id)
  INNER JOIN
   systypes as t
  ON ( c.system_type_id = t.xtype )
  WHERE
   o.type = 'U'
   AND o.name = @object_name
  ORDER BY
   o.name ,
   column_id
 OPEN Table_cursor
 FETCH NEXT FROM Table_cursor
 INTO @object_name , @column_name ,@is_nullable, @type ,
   @max_length, @precision , @scale

 WHILE @@FETCH_STATUS = 0
 BEGIN

  PRINT CHAR(9) + ' [' + @column_name + '] ' + CHAR(9) + @TYPE + ' ' + CHAR(9) + @is_nullable + ','
 
  FETCH NEXT FROM Table_cursor
  INTO @object_name , @column_name ,@is_nullable, @type ,
    @max_length, @precision , @scale
 END
 CLOSE Table_cursor
 DEALLOCATE Table_cursor


 PRINT CHAR(9) + ' [AUDIT_DATE] ' + CHAR(9) + ' DATETIME ' + CHAR(9) + ' ,'
 PRINT CHAR(9) + ' [AUDIT_USER] ' + CHAR(9) + ' VARCHAR(50) ' + CHAR(9) + ' ,'
 PRINT CHAR(9) + ' [AUDIT_APLICATION] ' + CHAR(9) + ' VARCHAR(255) ' + CHAR(9) + ' ,'
 PRINT CHAR(9) + ' [AUDIT_HOST] ' + CHAR(9) + ' VARCHAR(50) ' + CHAR(9) + ' NOT NULL,'
 PRINT CHAR(9) + ' [AUDIT_TIMESTAMP] ' + CHAR(9) + ' TIMESTAMP ' + CHAR(9) + ' ,'

 PRINT ' CONSTRAINT [' + @Audit_name + '_PK] PRIMARY KEY CLUSTERED '
 PRINT ' ('
 PRINT '  [AUDIT_ID] ASC'
 PRINT ' )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]'
 PRINT ' ) ON [PRIMARY]'
 PRINT ' '
 PRINT ' GO'
 PRINT ' '
 PRINT ' SET ANSI_PADDING OFF'
 PRINT ' GO'




 /********************************************************************************
  creacion del disparador
  Make a trigger
  
 ********************************************************************************/
 PRINT '-------- Script de creacion de triger de auditoria AUDIT_TR_' + @object_name+ '--------'
 PRINT '-------- Make a script of Audit trigger named AUDIT_TR_' + @object_name+ '--------'
 PRINT '-------- AUTOR: ' + USER_NAME()
 PRINT '-------- WORKPLACE: ' + HOST_NAME() + '/' + APP_NAME()

 PRINT 'SET ANSI_NULLS ON'
 PRINT 'GO'
 PRINT ''
 PRINT 'SET QUOTED_IDENTIFIER ON'
 PRINT 'GO'
 PRINT ''
 PRINT 'SET ANSI_PADDING ON'
 PRINT 'GO'
 PRINT ''
 PRINT 'CREATE TRIGGER ' + @trigger_name + ' '
 PRINT ' ON ' + @object_name + ' '
 PRINT ' AFTER INSERT, UPDATE, DELETE'
 PRINT ' AS'
 PRINT ' BEGIN'
 PRINT CHAR(9) + ' -- Verifico que tipo de accion se va a realizar'
 PRINT CHAR(9) + ' -- U : almaceno el dato a actualizar'
 PRINT CHAR(9) + ' -- D : almaceno el dato a ser eliminado'
 PRINT CHAR(9) + ' -- I : almaceno el dato a ser insertado'
 PRINT ''
 PRINT CHAR(9) + ' -- Verify the type of operatio to do'
 PRINT CHAR(9) + ' -- U : Save on update'
 PRINT CHAR(9) + ' -- D : Save on delete'
 PRINT CHAR(9) + ' -- I : save on insert'

 PRINT CHAR(9) + 'DECLARE @ACTION CHAR(1)'

 PRINT ''
 PRINT CHAR(9) + 'IF EXISTS(SELECT * FROM DELETED) AND EXISTS(SELECT * FROM INSERTED)'
 PRINT CHAR(9) + CHAR(9) + 'SET @ACTION = ''U'''
 PRINT CHAR(9) + 'ELSE IF EXISTS(SELECT * FROM DELETED) '
 PRINT CHAR(9) + CHAR(9) + 'SET @ACTION = ''D'' '
 PRINT CHAR(9) + 'ELSE IF EXISTS(SELECT * FROM INSERTED)'
 PRINT CHAR(9) + CHAR(9) + 'SET @ACTION = ''I'' '
 PRINT ''


 DECLARE @CAMPOS VARCHAR(8000)
 SET @CAMPOS = ''
 DECLARE Table_cursor CURSOR FOR
  SELECT
   c.name AS column_name
  FROM
   sys.objects AS o
  INNER JOIN
   sys.columns AS c
  ON ( o.object_id = c.object_id)
  WHERE
   o.type = 'U'
   AND o.name = @object_name
  ORDER BY
   o.name ,
   column_id
 OPEN Table_cursor
 FETCH NEXT FROM Table_cursor
 INTO @column_name

 WHILE @@FETCH_STATUS = 0
 BEGIN
  SET @CAMPOS = @CAMPOS + '' + ' [' + @column_name + '] ,'
 
  FETCH NEXT FROM Table_cursor
  INTO @column_name
 END
 CLOSE Table_cursor
 DEALLOCATE Table_cursor
 PRINT CHAR(9) + 'SET NOCOUNT ON'

 PRINT CHAR(9) + 'IF (@ACTION IN ( ''I'')) BEGIN'
 PRINT CHAR(9) + CHAR(9) + 'INSERT INTO ' + @Audit_name + ' '
 PRINT CHAR(9) + CHAR(9) + '( ' + @CAMPOS + '[AUDIT_ACTION], [AUDIT_DATE], [AUDIT_USER], [AUDIT_APLICATION],[AUDIT_HOST])'
 PRINT CHAR(9) + CHAR(9) + ' SELECT ' + @CAMPOS + '@ACTION, GetDate(), USER_NAME(), APP_NAME(), HOST_NAME() FROM INSERTED'
 PRINT CHAR(9) + 'END'

 PRINT CHAR(9) + 'ELSE IF (@ACTION IN (''U'', ''D'')) BEGIN'
 PRINT CHAR(9) + CHAR(9) + 'INSERT INTO ' + @Audit_name + ' '
 PRINT CHAR(9) + CHAR(9) + '( ' + @CAMPOS + '[AUDIT_ACTION], [AUDIT_DATE], [AUDIT_USER], [AUDIT_APLICATION],[AUDIT_HOST])'
 PRINT CHAR(9) + CHAR(9) + ' SELECT ' + @CAMPOS + '@ACTION, GetDate(), USER_NAME(), APP_NAME(), HOST_NAME() FROM DELETED'
 PRINT CHAR(9) + 'END'
 PRINT 'END'



 PRINT '---------------------------------------------------------'
 PRINT '--Informe de proceso'
 PRINT '---------------------------------------------------------'
 PRINT '--Se generò la tabla de auditoria ' + @Audit_name
 PRINT '--Se generò sobre tabla ' + @object_name + ' el trigger ' + @trigger_name + ' '
 PRINT '--'
 PRINT '--Copie el contenido de resultado a un archivo. sql'
 PRINT '--Nombre: xxx_creacion_auditoria_' + @object_name + '.sql'
 PRINT ''
 PRINT '---------------------------------------------------------'
 PRINT '--Proccess info'
 PRINT '---------------------------------------------------------'
 PRINT '--Made the audit table named ' + @Audit_name
 PRINT '--With the target table ' + @object_name + ' made the trigger ' + @trigger_name + ' '
 PRINT '--'
 PRINT '--Copy the result to a new query and save '
 PRINT '--Name: xxx_Create_Audit_' + @object_name + '.sql'
END
/*
Compatible SQL 2008
SQL 2008 compatible
*/

Bien venidos a SQL Dimention

2010-09-15T22:22:00.000-07:00

Ante todo bienvenidos al blog, simplemente aquí podrán tener herramientas de SQL server script que son difíciles de encontrar para el profano y que muchos atesoran como un bien de familia, la realidad es que compartir es el mejor camino para poder enriquecer aun más el conocimiento.

Saludos cordiales

Skynet Silverstar