https://www.c-sharpcorner.com/UploadFile/ff2f08/throw-in-sql-server-2012/
Etiqueta: sql
Links interesantes
Collation en SQL Server
Problema detectado: Error en las consultas porque las bases de datos tienen diferente intercalación
https://www.mssqltips.com/sqlservertip/4395/understanding-the-collate-databasedefault-clause-in-sql-server/
Ver codigo de un procedimiento almacenado (No SP_HELPTEXT)
select definition from sys.sql_modules where object_name(object_id) IN ('<<nombre proc>>')
Consultas utiles SQL Server
Buscar texto en todas las tablas de una base de datos SQL Server
Ejecutar lo siguiente
DECLARE @StrValorBusqueda nvarchar(100) SET @StrValorBusqueda='texto a buscar' DECLARE @Resultado TABLE (NombreColumna nvarchar(370), ValorColumna nvarchar(3630)) SET NOCOUNT ON DECLARE @NombreTabla nvarchar(256), @NombreColumna nvarchar(128), @StrValorBusqueda2 nvarchar(110) SET @NombreTabla = '' SET @StrValorBusqueda2 = QUOTENAME('%' + @StrValorBusqueda + '%','''') WHILE @NombreTabla IS NOT NULL BEGIN SET @NombreColumna = '' SET @NombreTabla = (SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE' AND QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @NombreTabla AND OBJECTPROPERTY( OBJECT_ID(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)), 'IsMSShipped') = 0) WHILE (@NombreTabla IS NOT NULL) AND (@NombreColumna IS NOT NULL) BEGIN SET @NombreColumna = (SELECT MIN(QUOTENAME(COLUMN_NAME)) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = PARSENAME(@NombreTabla, 2) AND TABLE_NAME = PARSENAME(@NombreTabla, 1) AND DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar') AND QUOTENAME(COLUMN_NAME) > @NombreColumna) IF @NombreColumna IS NOT NULL BEGIN INSERT INTO @Resultado EXEC ('SELECT ''' + @NombreTabla + '.' + @NombreColumna + ''', LEFT(' + @NombreColumna + ', 3630) FROM ' + @NombreTabla + ' (NOLOCK) ' + ' WHERE ' + @NombreColumna + ' LIKE ' + @StrValorBusqueda2) END END END SELECT NombreColumna, ValorColumna FROM @Resultado
funciones y comando sql no comunes
Generar clases .NET desde SQL
Clases entidad en base a tablas SQL
declare @TableName sysname = 'TableName' declare @Result varchar(max) = 'public class ' + @TableName + ' {' select @Result = @Result + ' public ' + ColumnType + NullableSign + ' ' + ColumnName + ' { get; set; } ' from ( select replace(col.name, ' ', '_') ColumnName, column_id ColumnId, case typ.name when 'bigint' then 'long' when 'binary' then 'byte[]' when 'bit' then 'bool' when 'char' then 'string' when 'date' then 'DateTime' when 'datetime' then 'DateTime' when 'datetime2' then 'DateTime' when 'datetimeoffset' then 'DateTimeOffset' when 'decimal' then 'decimal' when 'float' then 'float' when 'image' then 'byte[]' when 'int' then 'int' when 'money' then 'decimal' when 'nchar' then 'string' when 'ntext' then 'string' when 'numeric' then 'decimal' when 'nvarchar' then 'string' when 'real' then 'double' when 'smalldatetime' then 'DateTime' when 'smallint' then 'short' when 'smallmoney' then 'decimal' when 'text' then 'string' when 'time' then 'TimeSpan' when 'timestamp' then 'DateTime' when 'tinyint' then 'byte' when 'uniqueidentifier' then 'Guid' when 'varbinary' then 'byte[]' when 'varchar' then 'string' else 'UNKNOWN_' + typ.name end ColumnType, case when col.is_nullable = 1 and typ.name in ('bigint', 'bit', 'date', 'datetime', 'datetime2', 'datetimeoffset', 'decimal', 'float', 'int', 'money', 'numeric', 'real', 'smalldatetime', 'smallint', 'smallmoney', 'time', 'tinyint', 'uniqueidentifier') then '?' else '' end NullableSign from sys.columns col join sys.types typ on col.system_type_id = typ.system_type_id AND col.user_type_id = typ.user_type_id where object_id = object_id(@TableName) ) t order by ColumnId set @Result = @Result + ' }' print @Result
Generar objectos c# para DAO
declare @TableName sysname = 'NotaEntradaDetalle' declare @Result varchar(max) = '' select @Result = @Result + ' ocomando.Parameters.Add("@' + [ColumnName] + '", SqlDbType.' + ColumnType + ').Value=pItems.' + [ColumnName] + ';' from ( select replace(col.name, ' ', '_') ColumnName, column_id ColumnId, case typ.name when 'bigint' then 'BigInt' when 'binary' then 'Binary' when 'bit' then 'Bit' when 'char' then 'Char' when 'date' then 'DateTime' when 'datetime' then 'DateTime' when 'datetime2' then 'DateTime' when 'datetimeoffset' then 'DateTimeOffset' when 'decimal' then 'Decimal' when 'float' then 'Float' when 'image' then 'Image' when 'int' then 'Int' when 'money' then 'Money' when 'nchar' then 'NChar' when 'ntext' then 'NText' when 'numeric' then 'decimal' when 'nvarchar' then 'string' when 'real' then 'double' when 'smalldatetime' then 'DateTime' when 'smallint' then 'short' when 'smallmoney' then 'decimal' when 'text' then 'string' when 'time' then 'TimeSpan' when 'timestamp' then 'DateTime' when 'tinyint' then 'byte' when 'uniqueidentifier' then 'Guid' when 'varbinary' then 'byte[]' when 'varchar' then 'VarChar' else 'UNKNOWN_' + typ.name end ColumnType, case when col.is_nullable = 1 and typ.name in ('bigint', 'bit', 'date', 'datetime', 'datetime2', 'datetimeoffset', 'decimal', 'float', 'int', 'money', 'numeric', 'real', 'smalldatetime', 'smallint', 'smallmoney', 'time', 'tinyint', 'uniqueidentifier') then '?' else '' end NullableSign from sys.columns col join sys.types typ on col.system_type_id = typ.system_type_id AND col.user_type_id = typ.user_type_id where object_id = object_id(@TableName) ) t order by ColumnId set @Result = @Result + '' print @Result
Generando codigo para el reader
declare @TableName sysname = 'NotaEntradaDetalle' declare @Result varchar(max) = '' select @Result = @Result + ' objBE.' + [ColumnName] + '=(' + ColumnType + ')oDataReader["' + [ColumnName] + '"];' from ( select replace(col.name, ' ', '_') ColumnName, column_id ColumnId, case typ.name when 'bigint' then 'long' when 'binary' then 'byte[]' when 'bit' then 'bool' when 'char' then 'string' when 'date' then 'DateTime' when 'datetime' then 'DateTime' when 'datetime2' then 'DateTime' when 'datetimeoffset' then 'DateTimeOffset' when 'decimal' then 'decimal' when 'float' then 'float' when 'image' then 'byte[]' when 'int' then 'int' when 'money' then 'decimal' when 'nchar' then 'string' when 'ntext' then 'string' when 'numeric' then 'decimal' when 'nvarchar' then 'string' when 'real' then 'double' when 'smalldatetime' then 'DateTime' when 'smallint' then 'short' when 'smallmoney' then 'decimal' when 'text' then 'string' when 'time' then 'TimeSpan' when 'timestamp' then 'DateTime' when 'tinyint' then 'byte' when 'uniqueidentifier' then 'Guid' when 'varbinary' then 'byte[]' when 'varchar' then 'string' else 'UNKNOWN_' + typ.name end ColumnType, case when col.is_nullable = 1 and typ.name in ('bigint', 'bit', 'date', 'datetime', 'datetime2', 'datetimeoffset', 'decimal', 'float', 'int', 'money', 'numeric', 'real', 'smalldatetime', 'smallint', 'smallmoney', 'time', 'tinyint', 'uniqueidentifier') then '?' else '' end NullableSign from sys.columns col join sys.types typ on col.system_type_id = typ.system_type_id AND col.user_type_id = typ.user_type_id where object_id = object_id(@TableName) ) t order by ColumnId set @Result = @Result + '' print @Result
Generar numeros aleatorios en SQL
Eliminar log de transacciones sql server 2008 r2
ALTER DATABASE [DASHBOARD] SET RECOVERY SIMPLE; GO DBCC SHRINKFILE(DASHBOARD_log,1) GO ALTER DATABASE [DASHBOARD] SET RECOVERY FULL GO