SQL Server dia a dia


/ Published in: SQL
Save to your folder(s)

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


Copy this code and paste it in your HTML
  1. //Reiniciar el contador
  2. DBCC CHECKIDENT ('Schema.Tabla', RESEED,0)
  3.  
  4.  
  5. SELECT * INTO SchemaDestino.SACPaginasWeb FROM SchemaOrigen.INCPaginasWeb
  6.  
  7.  
  8. Obtener el ultimo d���­a del mes
  9.  
  10. SELECT eomonth('2015-02-01')
  11.  
  12. SELECT eomonth(getdate())
  13.  
  14. //Crear tablas, IDENTITY, FOREIGN KEY
  15.  
  16. CREATE TABLE dbo.escritor(
  17. id_escritor INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
  18. nombre VARCHAR(30) NOT NULL,
  19. apellido VARCHAR(40) NOT NULL,
  20. direccion VARCHAR(100) NULL
  21. )
  22.  
  23. CREATE TABLE dbo.poema(
  24. id_poema INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
  25. id_escritor INT FOREIGN KEY REFERENCES escritor(id_escritor),
  26. titulo VARCHAR(50) NOT NULL,
  27. contenido text,
  28. )
  29.  
  30. CREATE TABLE dbo.libro(
  31. id_libro INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
  32. titulo VARCHAR(100) NULL,
  33. precio NUMERIC(5,2) NULL
  34. )
  35.  
  36.  
  37. CREATE TABLE dbo.poema_libro(
  38. id_poema INT FOREIGN KEY REFERENCES poema(id_poema),
  39. id_libro INT FOREIGN KEY REFERENCES libro(id_libro)
  40. )
  41.  
  42.  
  43.  
  44. //Buscar un procedimiento o funcion Information Schema
  45.  
  46. SELECT *
  47. FROM INFORMATION_SCHEMA.ROUTINES
  48. WHERE ROUTINE_DEFINITION LIKE '%BuscaAltaFUMP%'
  49. AND ROUTINE_TYPE IN ('PROCEDURE','FUNCTION')
  50.  
  51. // Buscar un campo en una base de datos
  52. SELECT c.name AS 'ColumnName'
  53. ,t.name AS 'TableName'
  54. FROM sys.columns c
  55. JOIN sys.tables t ON c.object_id = t.object_id
  56. WHERE c.name LIKE '%id_escritor%'
  57. ORDER BY TableName
  58. ,ColumnName;
  59.  
  60. //CREATE tabla con para usar BOOLEAN
  61. DATABASE BOOLEAN? USE Instead
  62. Oracle No NUMBER(1)
  63. SQL Server No BIT
  64. MySQL No BIT OR TINYINT
  65. PostgreSQL Yes
  66.  
  67. CREATE TABLE dbo.practica (
  68. id_practica INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
  69. descripcion VARCHAR(50) NOT NULL,
  70. estatus bit NOT NULL DEFAULT 0
  71. )
  72.  
  73.  
  74.  
  75. //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.
  76. //Charindex
  77.  
  78. SELECT id_practica, len(descripcion), datalength(descripcion),descripcion, charindex(' ',RTRIM(LTRIM(descripcion)))
  79. FROM dbo.practica
  80. WHERE charindex(' ',RTRIM(LTRIM(descripcion)))>0
  81.  
  82.  
  83. //Gererar las clases de la capa Entity
  84.  
  85. --#Region "Propiedades"
  86.  
  87. SELECT
  88. concat('Public Property ', column_name, '() as ', DATA_TYPE )
  89. FROM INFORMATION_SCHEMA.Columns
  90. WHERE TABLE_NAME='SACBIENES'
  91.  
  92. --#End Region
  93.  
  94.  
  95. --Revision de conversion sea correcta
  96. SELECT
  97. concat('Byval ', column_name, ' as ', DATA_TYPE ) AS TYPESQL,
  98. concat('Byval ', column_name, ' as ', net.dbtype ) AS TYPENET
  99. FROM INFORMATION_SCHEMA.Columns
  100. LEFT JOIN ActivoFijo.SQLDOTNET net ON DATA_TYPE = net.SqlDbType
  101. WHERE TABLE_NAME='SACBIENES'
  102.  
  103.  
  104. --#Region "Constructores"
  105.  
  106. --Variables
  107. DECLARE @VARIABLES AS NVARCHAR(500);
  108. SET @VARIABLES = (
  109. SELECT STUFF(
  110. (
  111. SELECT
  112. RTRIM( LTRIM(
  113. concat(',', 'Byval ', column_name, ' as System.', net.dbtype ,' ')
  114. ))
  115.  
  116. FROM INFORMATION_SCHEMA.Columns
  117. LEFT JOIN ActivoFijo.SQLDOTNET net ON DATA_TYPE = net.SqlDbType
  118. WHERE TABLE_NAME='SACBIENES'
  119.  
  120. FOR XML PATH (''))
  121. , 1, 1, '') AS CCSR)
  122.  
  123.  
  124. --select @variables
  125. SELECT CONCAT('Public Sub New(', @VARIABLES , ASCII(10),') End Sub')
  126.  
  127.  
  128.  
  129. --Asignaciones
  130. SELECT
  131. concat('Me.', column_name, ' = ', column_name )
  132. FROM INFORMATION_SCHEMA.Columns
  133. WHERE TABLE_NAME='SACBIENES'
  134.  
  135.  
  136. --#End Region
  137.  
  138.  
  139. -----------------------------------------------------------------------------------
  140. -- CAPA BL
  141. -- #Region "Propiedades"
  142. DECLARE @salto AS VARCHAR= CHAR(13) + CHAR(10);
  143. SELECT
  144. RTRIM( LTRIM(
  145. concat('Public Property ', column_name, '() as System.', net.dbtype , @salto ,
  146. ' Get ' , @salto ,
  147. ' Return Me.',TABLE_NAME, '.', column_name , @salto,
  148. ' End Get ', @salto,
  149. ' Set (value as System.', net.dbtype ,')', @salto,
  150. ' Me.',TABLE_NAME, '.', column_name ,' = value ', @salto,
  151. 'End Set ', @salto,
  152. 'End Property', @salto
  153.  
  154. )
  155. ))
  156.  
  157. FROM INFORMATION_SCHEMA.Columns
  158. LEFT JOIN ActivoFijo.SQLDOTNET net ON DATA_TYPE = net.SqlDbType
  159. WHERE TABLE_NAME='SACBIENES'
  160.  
  161. --#End Region
  162.  
  163.  
  164.  
  165. --#Region "Constructores"
  166.  
  167. --Variables
  168. DECLARE @saltoC AS VARCHAR= CHAR(13) + CHAR(10);
  169. DECLARE @variablesC AS NVARCHAR(1000);
  170. SET @variablesC = (
  171. SELECT STUFF(
  172. (
  173. SELECT
  174. RTRIM( LTRIM(
  175. concat(',', 'Byval ', column_name, ' as System.', net.dbtype ,' ')
  176. ))
  177.  
  178. FROM INFORMATION_SCHEMA.Columns
  179. LEFT JOIN ActivoFijo.SQLDOTNET net ON DATA_TYPE = net.SqlDbType
  180. WHERE TABLE_NAME='SACBIENES'
  181.  
  182. FOR XML PATH (''))
  183. , 1, 1, '') AS CCSR)
  184.  
  185.  
  186. SELECT CONCAT('Sub New()'+@saltoC +'End Sub'+@saltoC+'Public Sub New(', @variablesC , ASCII(10),')' + @saltoC + ' End Sub')
  187.  
  188.  
  189. --Asignaciones
  190. SELECT
  191. concat('Me.', column_name, ' = ', column_name )
  192. FROM INFORMATION_SCHEMA.Columns
  193. WHERE TABLE_NAME='SACBIENES'
  194.  
  195.  
  196.  
  197.  
  198. --#End Region
  199.  
  200.  
  201. USE ALMACEN
  202. ALTER DATABASE ALMACEN SET MULTI_USER

Report this snippet


Comments

RSS Icon Subscribe to comments

You need to login to post a comment.