//Reiniciar el contador
DBCC CHECKIDENT ('Schema.Tabla', RESEED,0) 
 
 
SELECT * INTO SchemaDestino.SACPaginasWeb FROM SchemaOrigen.INCPaginasWeb
 
 
Obtener el ultimo d���­a del mes
 
SELECT eomonth('2015-02-01')
 
SELECT eomonth(getdate())
 
//Crear tablas, IDENTITY, FOREIGN KEY
 
CREATE TABLE dbo.escritor(
id_escritor INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
nombre VARCHAR(30) NOT NULL,
apellido VARCHAR(40)  NOT NULL,
direccion VARCHAR(100) NULL
)
 
CREATE TABLE dbo.poema(
id_poema INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
id_escritor INT FOREIGN KEY REFERENCES escritor(id_escritor),
titulo VARCHAR(50) NOT NULL,
contenido text,
)
 
CREATE TABLE dbo.libro(
id_libro INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
titulo VARCHAR(100) NULL,
precio NUMERIC(5,2) NULL
)
 
 
CREATE TABLE dbo.poema_libro(
id_poema INT FOREIGN KEY REFERENCES poema(id_poema),
id_libro INT FOREIGN KEY REFERENCES libro(id_libro)
)
 
 
 
//Buscar un procedimiento o funcion Information Schema
 
SELECT *
    FROM INFORMATION_SCHEMA.ROUTINES 
WHERE ROUTINE_DEFINITION LIKE '%BuscaAltaFUMP%' 
    AND ROUTINE_TYPE IN ('PROCEDURE','FUNCTION')
 
// Buscar un campo en una base de datos
		 SELECT      c.name  AS 'ColumnName'
               ,t.name AS 'TableName'
FROM        sys.columns c
JOIN        sys.tables  t   ON c.object_id = t.object_id
WHERE       c.name LIKE '%id_escritor%'
ORDER BY    TableName
               ,ColumnName;
 
//CREATE tabla con para usar BOOLEAN 
DATABASE	BOOLEAN?	USE Instead
Oracle	         No	NUMBER(1)
SQL Server	 No	BIT
MySQL	         No	BIT OR TINYINT
PostgreSQL	Yes
 
CREATE TABLE dbo.practica (
id_practica INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
descripcion VARCHAR(50) NOT NULL,
estatus bit NOT NULL DEFAULT 0
)
 
 
 
//Esta función busca una expresión de carácter dentro de una segunda expresión de carácter y, si la encuentra, devuelve la posición inicial de la primera expresión.
//Charindex
 
SELECT id_practica, len(descripcion), datalength(descripcion),descripcion, charindex(' ',RTRIM(LTRIM(descripcion))) 
FROM dbo.practica
WHERE charindex(' ',RTRIM(LTRIM(descripcion)))>0
 
 
//Gererar las clases de la capa Entity
 
--#Region "Propiedades"
 
 SELECT 
 concat('Public Property ', column_name, '() as ', DATA_TYPE )
 FROM INFORMATION_SCHEMA.Columns 
 WHERE TABLE_NAME='SACBIENES'
 
--#End Region
 
 
--Revision de conversion sea correcta
 SELECT 
  concat('Byval ', column_name, ' as ', DATA_TYPE ) AS TYPESQL,
  concat('Byval ', column_name, ' as ', net.dbtype ) AS TYPENET
 FROM INFORMATION_SCHEMA.Columns 
 LEFT JOIN ActivoFijo.SQLDOTNET net ON DATA_TYPE = net.SqlDbType
 WHERE TABLE_NAME='SACBIENES'
 
 
 --#Region "Constructores" 
 
 --Variables
DECLARE @VARIABLES AS NVARCHAR(500); 
SET @VARIABLES = (
SELECT STUFF(
											(
											SELECT 
RTRIM( LTRIM(
 concat(',', 'Byval ', column_name, ' as System.', net.dbtype ,' ')
 ))
 
 FROM INFORMATION_SCHEMA.Columns 
 LEFT JOIN ActivoFijo.SQLDOTNET net ON DATA_TYPE = net.SqlDbType
 WHERE TABLE_NAME='SACBIENES'
 
										FOR XML PATH (''))
										, 1, 1, '') AS CCSR) 
 
 
		--select @variables 
		SELECT CONCAT('Public Sub New(', @VARIABLES , ASCII(10),') End Sub')
 
 
 
--Asignaciones 
 SELECT 
  concat('Me.', column_name, ' = ', column_name )
 FROM INFORMATION_SCHEMA.Columns 
 WHERE TABLE_NAME='SACBIENES'
 
 
--#End Region
 
 
-----------------------------------------------------------------------------------
-- CAPA BL
-- #Region "Propiedades"
DECLARE @salto AS VARCHAR= CHAR(13) + CHAR(10);
SELECT 
RTRIM( LTRIM(
 concat('Public Property ', column_name, '() as System.', net.dbtype , @salto ,
 ' Get ' , @salto ,
 ' Return Me.',TABLE_NAME, '.', column_name , @salto,
 ' End Get ', @salto, 
 ' Set (value as System.', net.dbtype ,')', @salto,
 '  Me.',TABLE_NAME, '.', column_name ,' = value ', @salto,
 'End Set ', @salto,
 'End Property', @salto
 
 )
 ))
 
 FROM INFORMATION_SCHEMA.Columns 
 LEFT JOIN ActivoFijo.SQLDOTNET net ON DATA_TYPE = net.SqlDbType
 WHERE TABLE_NAME='SACBIENES'
 
 --#End Region
 
 
 
 --#Region "Constructores" 
 
 --Variables
DECLARE @saltoC AS VARCHAR= CHAR(13) + CHAR(10);
DECLARE @variablesC AS NVARCHAR(1000); 
SET @variablesC = (
SELECT STUFF(
											(
											SELECT 
RTRIM( LTRIM(
 concat(',', 'Byval ', column_name, ' as System.', net.dbtype ,' ')
 ))
 
 FROM INFORMATION_SCHEMA.Columns 
 LEFT JOIN ActivoFijo.SQLDOTNET net ON DATA_TYPE = net.SqlDbType
 WHERE TABLE_NAME='SACBIENES'
 
										FOR XML PATH (''))
										, 1, 1, '') AS CCSR) 
 
 
		SELECT CONCAT('Sub New()'+@saltoC +'End Sub'+@saltoC+'Public Sub New(', @variablesC , ASCII(10),')' + @saltoC  + ' End Sub')
 
 
--Asignaciones 
 SELECT 
  concat('Me.', column_name, ' = ', column_name )
 FROM INFORMATION_SCHEMA.Columns 
 WHERE TABLE_NAME='SACBIENES'
 
 
 
 
--#End Region
 
 
USE ALMACEN
ALTER DATABASE ALMACEN SET MULTI_USER
 
 
 
--- Mostrar los Trigger que estan en la BD
SELECT * FROM sysobjects WHERE TYPE='TR'