SQL Server dia a dia - SQL Snipplr Social Repository

Revision: 94331

at April 11, 2024 16:44 by systemergon

Updated Code

//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'

Revision: 89149

at May 16, 2022 23:34 by systemergon

Updated Code

//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

Revision: 88878

at April 11, 2022 16:31 by systemergon

Updated Code

//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

Revision: 88876

at April 11, 2022 15:54 by systemergon

Updated Code

//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
)





//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

Revision: 88875

at April 11, 2022 15:32 by systemergon

Updated Code

//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;


//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

Revision: 88874

at April 11, 2022 15:23 by systemergon

Updated Title

SQL Server dia a dia

Updated URL

Updated Code

//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)
)





//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

Updated Description

De las tareas que dia a dia necesitamos realizar en una base de datos

Revision: 70785

at March 11, 2017 05:03 by systemergon

Updated Code

//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())


//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

Revision: 70784

at August 5, 2016 05:14 by systemergon

Updated Code

select * into SchemaDestino.SACPaginasWeb from SchemaOrigen.INCPaginasWeb


Obtener el ultimo dÃƒï¿½Ã‚Âa del mes

select eomonth('2015-02-01')

select eomonth(getdate())


//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

Revision: 70783

at August 4, 2016 05:50 by systemergon

Updated Code

select * into SchemaDestino.SACPaginasWeb from SchemaOrigen.INCPaginasWeb


Obtener el ultimo dÃƒÂa del mes

select eomonth('2015-02-01')

select eomonth(getdate())


//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

Revision: 70782

at August 4, 2016 02:15 by systemergon

Updated Code

select * into SchemaDestino.SACPaginasWeb from SchemaOrigen.INCPaginasWeb


Obtener el ultimo dÃa del mes

select eomonth('2015-02-01')

select eomonth(getdate())

Revision: 70781

at July 8, 2016 01:24 by systemergon

Initial Code

select * into SchemaDestino.SACPaginasWeb from SchemaOrigen.INCPaginasWeb

Initial URL

Initial Description

De las tareas que dÃa a dÃa necesitamos realizar en una base de datos

Initial Title

SQL Server dÃa a dÃa

Initial Tags

copy

Initial Language

SQL

Choose a language for easy browsing: