https://www.c-sharpcorner.com/UploadFile/ff2f08/throw-in-sql-server-2012/
Categoría: sql server
Usar GSM Modem para enviar mensajes SMS C#
Crear clase C# desde tabla SQL
Usar lo siguiente
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 'char'
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
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
Ver log de envio de correos sql mail
SENT MESSAGES LOG
SELECT TOP 20 * FROM [msdb].[dbo].[sysmail_sentitems] ORDER BY [send_request_date] DESC
FAILED MESSAGES LOG
SELECT TOP 20 * FROM [msdb].[dbo].[sysmail_faileditems] ORDER BY [send_request_date] DESC
ALL MESSAGES – REGARDLESS OF STATUS
SELECT TOP 20 * FROM [msdb].[dbo].[sysmail_allitems] ORDER BY [send_request_date] DESC
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