public void CrearIndex(DataTable pTablas, DataTable pIndices, string pUser) { ClsReportes ClsRepo = new ClsReportes(); DataTable dtResultado = new DataTable(); string vSql = string.Empty; Conexion.ClsConexionLocal vConexion = new Conexion.ClsConexionLocal(); int vContador = 0; DateTime vFecha; string vNomTabla; try { foreach (DataRow vRows in pTablas.Rows) { vSql = string.Empty; foreach (DataRow vRow in pIndices.Select("NombreTabla = '" + vRows["NombreTabla"].ToString() + "'")) { if (vContador == 0) { vFecha = DateTime.Now; if (vRow["TipoIndex"].ToString() == "C") { vSql += "CREATE CLUSTERED INDEX [ClusteredIndex" + String.Format("{0:ddMMyyyyHH:mm:ss.ff}", vFecha) + "] ON dbo." + vRow["NombreTabla"].ToString() + " ("; //Variable aqui vNomTabla = vRow["NombreTabla"].ToString(); } else { vSql += "CREATE NONCLUSTERED INDEX [noClusteredIndex" + String.Format("{0:ddMMyyyyHH:mm:ss.ff}", vFecha) + "] ON dbo." + vRow["NombreTabla"].ToString() + " ("; //Variable aqui vNomTabla = vRow["NombreTabla"].ToString(); } ClsRepo.repoIndices(vNomTabla); } vSql += vRow["NombreCampo"].ToString() + ","; vContador++; } vContador = 0; vSql = vSql.TrimEnd(Convert.ToChar(",")); vSql += ")"; vConexion.ejecutarInsert(pUser + " " + vSql); } } catch (Exception ex) { throw new Exception(ex.Message, ex); } }
public DataTable ValidarIndex(string pNombreTabla, string pUser) { DataTable dtResultado = new DataTable(); string vSql = string.Empty; Conexion.ClsConexionLocal vConexion = new Conexion.ClsConexionLocal(); try { vSql = pUser + " Exec sp_helpindex '" + pNombreTabla + "'"; dtResultado = vConexion.ejecutar(vSql); } catch (Exception ex) { throw new Exception(ex.Message, ex); } return(dtResultado); }
public DataTable DatosTreeview() { DataTable vResultado = new DataTable(); string sql = ""; try { //sql = " DECLARE @id INT,"; //sql = sql + " @count INT,"; //sql = sql + " @vBaseDatos VARCHAR(50),"; //sql = sql + " @vQuery NVARCHAR(1024),"; //sql = sql + " @vParametros NVARCHAR(255),"; //sql = sql + " @vValorParametro VARCHAR(1),"; //sql = sql + " @vContador INT,"; //sql = sql + " @vCodigoPadre INT,"; //sql = sql + " @vCantidadTablas INT,"; //sql = sql + " @vNombreTabla VARCHAR(50)"; //sql = sql + " DECLARE @vDatos TABLE(Codigo INT IDENTITY(1, 1),"; //sql = sql + " CodigoPadre INT,"; //sql = sql + " Descripcion VARCHAR(50))"; //sql = sql + " DECLARE @vTablas TABLE(Codigo INT,"; //sql = sql + " CodigoPadre INT,"; //sql = sql + " Descripcion VARCHAR(50))"; //sql = sql + " DECLARE @vColumnas TABLE(CodigoPadre INT,"; //sql = sql + " Tabla VARCHAR(50),"; //sql = sql + " Columna VARCHAR(50))"; //sql = sql + " SET @vContador = 1"; //sql = sql + " SET @id = 1"; //sql = sql + " SET @vQuery = ''"; //sql = sql + " SET @vParametros = '@vTipo VARCHAR(1)'"; //sql = sql + " SET @vValorParametro = 'U'"; //sql = sql + " SELECT @count = COUNT(1) FROM sys.databases WHERE database_id >= 5"; //sql = sql + " WHILE @id<= @count"; //sql = sql + " BEGIN"; //sql = sql + " SELECT @vBaseDatos = name"; //sql = sql + " FROM(SELECT name, RANK()OVER(ORDER BY name ASC)AS RANK FROM sys.databases WHERE database_id >= 5) TBS"; //sql = sql + " WHERE RANK = @id"; //sql = sql + " INSERT INTO @vDatos(CodigoPadre, Descripcion)"; //sql = sql + " VALUES(0, @vBaseDatos)"; //sql = sql + " SET @vQuery = 'SELECT ' + CONVERT(VARCHAR(100),@@IDENTITY) + ', name FROM ' + @vBaseDatos + '.sys.objects WHERE type = @vTipo'"; //sql = sql + " INSERT INTO @vDatos(CodigoPadre, Descripcion)"; //sql = sql + " EXEC sp_executesql @vQuery, @vParametros, @vValorParametro"; //sql = sql + " INSERT INTO @vTablas(Codigo, CodigoPadre, Descripcion)"; //sql = sql + " select Codigo, CodigoPadre, Descripcion from @vDatos where CodigoPadre <> 0"; //sql = sql + " SELECT @vCantidadTablas = COUNT(1) FROM @vTablas"; //sql = sql + " WHILE @vContador<= @vCantidadTablas"; //sql = sql + " BEGIN"; //sql = sql + " DELETE @vColumnas"; //sql = sql + " SELECT @vNombreTabla = Descripcion,@vCodigoPadre = Codigo"; //sql = sql + " FROM(SELECT Descripcion, Codigo, RANK()OVER(ORDER BY Descripcion ASC)AS RANK FROM @vTablas) TBS"; //sql = sql + " WHERE RANK = @vContador"; //sql = sql + " SET @vQuery = 'SELECT' + char(39) + CONVERT(VARCHAR(20), @vCodigoPadre) + Char(39) + ', TABLE_NAME, COLUMN_NAME FROM ' + @vBaseDatos + '.INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = ' + CHAR(39) + @vNombreTabla + CHAR(39)"; //sql = sql + " INSERT INTO @vColumnas"; //sql = sql + " EXEC(@vQuery)"; //sql = sql + " INSERT INTO @vDatos(CodigoPadre, Descripcion)"; //sql = sql + " SELECT A.CodigoPadre,"; //sql = sql + " A.Columna"; //sql = sql + " FROM @vColumnas A"; //sql = sql + " SET @vContador += 1"; //sql = sql + " END"; //sql = sql + " SET @id = @id + 1"; //sql = sql + " END"; //sql = sql + " SELECT Codigo,"; //sql = sql + " CodigoPadre,"; //sql = sql + " Descripcion"; //sql = sql + " FROM @vDatos"; //sql = " DECLARE @id INT,"; //sql = sql + " @count INT,"; //sql = sql + " @vBaseDatos VARCHAR(50),"; //sql = sql + " @vQuery NVARCHAR(1024),"; //sql = sql + " @vParametros NVARCHAR(255),"; //sql = sql + " @vValorParametro VARCHAR(1),"; //sql = sql + " @vContador INT,"; //sql = sql + " @vCantidadTablas INT,"; //sql = sql + " @vNombreTabla VARCHAR(50)"; //sql = sql + " DECLARE @vDatos TABLE(Codigo INT IDENTITY(1, 1),"; //sql = sql + " CodigoPadre INT,"; //sql = sql + " Descripcion VARCHAR(50))"; //sql = sql + " DECLARE @vTablas TABLE(CodigoPadre INT,"; //sql = sql + " Descripcion VARCHAR(50))"; //sql = sql + " DECLARE @vColumnas TABLE(Tabla VARCHAR(50),"; //sql = sql + " Columna VARCHAR(50))"; //sql = sql + " SET @vContador = 1"; //sql = sql + " SET @id = 1"; //sql = sql + " SET @vQuery = ''"; //sql = sql + " SET @vParametros = '@vTipo VARCHAR(1)'"; //sql = sql + " SET @vValorParametro = 'U'"; //sql = sql + " SELECT @count = COUNT(1) FROM sys.databases WHERE database_id >= 5"; //sql = sql + " WHILE @id<= @count"; //sql = sql + " BEGIN"; //sql = sql + " SELECT @vBaseDatos = name"; //sql = sql + " FROM(SELECT name, RANK()OVER(ORDER BY name ASC)AS RANK FROM sys.databases WHERE database_id >= 5) TBS"; //sql = sql + " WHERE RANK = @id"; //sql = sql + " INSERT INTO @vDatos(CodigoPadre, Descripcion)"; //sql = sql + " VALUES(0, @vBaseDatos)"; //sql = sql + " SET @vQuery = 'SELECT ' + CONVERT(VARCHAR(100),@@IDENTITY) + ', name FROM ' + @vBaseDatos + '.sys.objects WHERE type = @vTipo'"; //sql = sql + " INSERT INTO @vDatos(CodigoPadre, Descripcion)"; //sql = sql + " EXEC sp_executesql @vQuery, @vParametros, @vValorParametro"; //sql = sql + " INSERT INTO @vTablas(CodigoPadre, Descripcion)"; //sql = sql + " EXEC sp_executesql @vQuery, @vParametros, @vValorParametro"; //sql = sql + " SELECT @vCantidadTablas = COUNT(1) FROM @vTablas"; //sql = sql + " WHILE @vContador<= @vCantidadTablas"; //sql = sql + " BEGIN"; //sql = sql + " DELETE @vColumnas"; //sql = sql + " SELECT @vNombreTabla = Descripcion"; //sql = sql + " FROM(SELECT Descripcion, RANK()OVER(ORDER BY Descripcion ASC)AS RANK FROM @vTablas) TBS"; //sql = sql + " WHERE RANK = @vContador"; //sql = sql + " SET @vQuery = 'SELECT TABLE_NAME, COLUMN_NAME FROM ' + @vBaseDatos + '.INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = ' + CHAR(39) + @vNombreTabla + CHAR(39)"; //sql = sql + " INSERT INTO @vColumnas"; //sql = sql + " EXEC(@vQuery)"; //sql = sql + " INSERT INTO @vDatos(CodigoPadre, Descripcion)"; //sql = sql + " SELECT B.Codigo,"; //sql = sql + " A.Columna"; //sql = sql + " FROM @vColumnas A"; //sql = sql + " INNER JOIN @vDatos B ON"; //sql = sql + " A.Tabla = B.Descripcion"; //sql = sql + " SET @vContador += 1"; //sql = sql + " END"; //sql = sql + " SET @id = @id + 1"; //sql = sql + " END"; //sql = sql + " SELECT Codigo,"; //sql = sql + " CodigoPadre,"; //sql = sql + " Descripcion"; //sql = sql + " FROM @vDatos"; sql = "DECLARE @id INT,"; sql = sql + " @count INT,"; sql = sql + " @vBaseDatos VARCHAR(50),"; sql = sql + " @vQuery NVARCHAR(1024),"; sql = sql + " @vParametros NVARCHAR(255),"; sql = sql + " @vValorParametro VARCHAR(1)"; sql = sql + " DECLARE @vDatos TABLE( Codigo INT,"; sql = sql + " CodigoPadre INT,"; sql = sql + " Descripcion VARCHAR(50))"; sql = sql + " SET @id=1"; sql = sql + " SET @vQuery = ''"; sql = sql + " SET @vParametros = '@vTipo VARCHAR(1)'"; sql = sql + " SET @vValorParametro= 'U'"; sql = sql + " SELECT @count = COUNT(1) FROM sys.databases WHERE database_id >=5"; sql = sql + " WHILE @id<=@count"; sql = sql + " BEGIN"; sql = sql + " SELECT @vBaseDatos = name "; sql = sql + " FROM (SELECT name,RANK()OVER (ORDER BY name ASC)AS RANK FROM sys.databases WHERE database_id >=5) TBS "; sql = sql + " WHERE RANK=@id"; sql = sql + " INSERT INTO @vDatos(Codigo,CodigoPadre,Descripcion)"; sql = sql + " VALUES (@id,0,@vBaseDatos)"; sql = sql + " SET @vQuery = 'SELECT 0, ' + CONVERT(VARCHAR(1000),@id) + ', name FROM ' + @vBaseDatos + '.sys.objects WHERE type = @vTipo' "; sql = sql + " INSERT INTO @vDatos(Codigo,CodigoPadre,Descripcion)"; sql = sql + " EXEC sp_executesql @vQuery, @vParametros, @vValorParametro"; sql = sql + " SET @id=@id+1"; sql = sql + " END"; sql = sql + " SELECT Codigo,"; sql = sql + " CodigoPadre,"; sql = sql + " Descripcion"; sql = sql + " FROM @vDatos"; Conexion.ClsConexionLocal vConexion = new Conexion.ClsConexionLocal(); vResultado = vConexion.ejecutar(sql); return(vResultado); } catch (Exception ex) { throw new Exception(ex.Message, ex); } }