Posted By

systemergon on 07/08/16


Tagged

copy Consultas Seleccion Duplicar


Versions (?)

SQL Server día a día


 / Published in: SQL
 

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

  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.  
  15. //Gererar las clases de la capa Entity
  16.  
  17. --#Region "Propiedades"
  18.  
  19. SELECT
  20. concat('Public Property ', column_name, '() as ', DATA_TYPE )
  21. FROM INFORMATION_SCHEMA.COLUMNS
  22. WHERE Table_name='SACBIENES'
  23.  
  24. --#End Region
  25.  
  26.  
  27. --Revision de conversion sea correcta
  28. SELECT
  29. concat('Byval ', column_name, ' as ', DATA_TYPE ) AS TYPESQL,
  30. concat('Byval ', column_name, ' as ', net.dbtype ) AS TYPENET
  31. FROM INFORMATION_SCHEMA.COLUMNS
  32. LEFT JOIN ActivoFijo.SQLDOTNET net ON DATA_TYPE = net.SqlDbType
  33. WHERE Table_name='SACBIENES'
  34.  
  35.  
  36. --#Region "Constructores"
  37.  
  38. --Variables
  39. DECLARE @VARIABLES AS NVARCHAR(500);
  40. SET @VARIABLES = (
  41. SELECT STUFF(
  42. (
  43. SELECT
  44. RTRIM( LTRIM(
  45. concat(',', 'Byval ', column_name, ' as System.', net.dbtype ,' ')
  46. ))
  47.  
  48. FROM INFORMATION_SCHEMA.COLUMNS
  49. LEFT JOIN ActivoFijo.SQLDOTNET net ON DATA_TYPE = net.SqlDbType
  50. WHERE Table_name='SACBIENES'
  51.  
  52. FOR XML PATH (''))
  53. , 1, 1, '') AS CCSR)
  54.  
  55.  
  56. --select @variables
  57. SELECT CONCAT('Public Sub New(', @VARIABLES , ASCII(10),') End Sub')
  58.  
  59.  
  60.  
  61. --Asignaciones
  62. SELECT
  63. concat('Me.', column_name, ' = ', column_name )
  64. FROM INFORMATION_SCHEMA.COLUMNS
  65. WHERE Table_name='SACBIENES'
  66.  
  67.  
  68. --#End Region
  69.  
  70.  
  71. -----------------------------------------------------------------------------------
  72. -- CAPA BL
  73. -- #Region "Propiedades"
  74. Declare @salto AS varchar= CHAR(13) + CHAR(10);
  75. SELECT
  76. RTRIM( LTRIM(
  77. concat('Public Property ', column_name, '() as System.', net.dbtype , @salto ,
  78. ' Get ' , @salto ,
  79. ' Return Me.',Table_name, '.', column_name , @salto,
  80. ' End Get ', @salto,
  81. ' Set (value as System.', net.dbtype ,')', @salto,
  82. ' Me.',Table_name, '.', column_name ,' = value ', @salto,
  83. 'End Set ', @salto,
  84. 'End Property', @salto
  85.  
  86. )
  87. ))
  88.  
  89. FROM INFORMATION_SCHEMA.COLUMNS
  90. LEFT JOIN ActivoFijo.SQLDOTNET net ON DATA_TYPE = net.SqlDbType
  91. WHERE Table_name='SACBIENES'
  92.  
  93. --#End Region
  94.  
  95.  
  96.  
  97. --#Region "Constructores"
  98.  
  99. --Variables
  100. Declare @saltoC AS varchar= CHAR(13) + CHAR(10);
  101. DECLARE @variablesC AS NVARCHAR(1000);
  102. SET @variablesC = (
  103. SELECT STUFF(
  104. (
  105. SELECT
  106. RTRIM( LTRIM(
  107. concat(',', 'Byval ', column_name, ' as System.', net.dbtype ,' ')
  108. ))
  109.  
  110. FROM INFORMATION_SCHEMA.COLUMNS
  111. LEFT JOIN ActivoFijo.SQLDOTNET net ON DATA_TYPE = net.SqlDbType
  112. WHERE Table_name='SACBIENES'
  113.  
  114. FOR XML PATH (''))
  115. , 1, 1, '') AS CCSR)
  116.  
  117.  
  118. SELECT CONCAT('Sub New()'+@saltoC +'End Sub'+@saltoC+'Public Sub New(', @variablesC , ASCII(10),')' + @saltoC + ' End Sub')
  119.  
  120.  
  121. --Asignaciones
  122. SELECT
  123. concat('Me.', column_name, ' = ', column_name )
  124. FROM INFORMATION_SCHEMA.COLUMNS
  125. WHERE Table_name='SACBIENES'
  126.  
  127.  
  128.  
  129.  
  130. --#End Region

Report this snippet  

You need to login to post a comment.