254. SQL Script - Ubicación y tamaño de las bases de datos de SQL

 Como parte del mantenimiento de una base de datos, es importante saber en donde se están almacenando los recursos, estos para poder planear el espacio necesario en caso de crecimiento, para ello les recomiendo e siguiente query:

select sf.name, si.name, si.rows,sf.growth, sf.filename, sf.fileid,  sfg.groupid, sfg.groupname

from sysindexes si inner join sysfiles sf on si.groupid = sf.groupid

inner join sysfilegroups sfg on sf.groupid = sfg.groupid

Order by sf.filename

Felices lineas

242 - Claves foráneas SQL Server

La forma facil de obtener las tablas relacionadas a una tabla en SQL Server es

exec sp_fkeys 'Tabla'

Esto nos mostrara las relaciones de una tabla con otras tablas en la base de datos


Felices lineas

237. Query para obtener todas las relaciones existentes entre las tablas de sql server

Hola:

Les dejo un query para obtener todas las relaciones que hay entre tablas de sql server

SELECT
    fk.name 'FK Name',
    tp.name 'Tabla padre',
    cp.name, cp.column_id,
    tr.name 'Tabla de referencia',
    cr.name, cr.column_id
FROM 
    sys.foreign_keys fk
INNER JOIN 
    sys.tables tp ON fk.parent_object_id = tp.object_id
INNER JOIN 
    sys.tables tr ON fk.referenced_object_id = tr.object_id
INNER JOIN 
    sys.foreign_key_columns fkc ON fkc.constraint_object_id = fk.object_id
INNER JOIN 
    sys.columns cp ON fkc.parent_column_id = cp.column_id AND fkc.parent_object_id = cp.object_id
INNER JOIN 
    sys.columns cr ON fkc.referenced_column_id = cr.column_id AND fkc.referenced_object_id = cr.object_id
ORDER BY
    tp.name, cp.column_id


Felices Lineas

236. Query para obtener el tamaño de las tablas SQL

Hola

Les dejo este query que permite que sepan que cantidad de espacio y cantidad de registros ocupan las tablas en una base de datos

DECLARE @TableName VARCHAR(100)  
DECLARE tableCursor CURSOR
FOR
select [name]
from dbo.sysobjects
where  OBJECTPROPERTY(id, N'IsUserTable') = 1
FOR READ ONLY
CREATE TABLE #TempTable
(
    Nombre_tabla varchar(100),
    Renglones int,
    Espacio_reservado varchar(50),
    Tamano_datos varchar(50),
    Tamano_indice varchar(50),
    Espaciosinusar varchar(50)
)
OPEN tableCursor
FETCH NEXT FROM tableCursor INTO @TableName
WHILE (@@Fetch_Status = 0)
BEGIN
    INSERT  #TempTable
        EXEC sp_spaceused @TableName
    FETCH NEXT FROM tableCursor INTO @TableName
END
CLOSE tableCursor
DEALLOCATE tableCursor
SELECT *
FROM #TempTable order by Renglones desc
DROP TABLE #TempTable

Felices Lineas

183. Enviar la respuesta de un insert hacia una tabla

Las inserciones y actualizaciones son capaces de regresar un conjunto de datos, pero estos datos los responede en forma de una tabla, de esta forma para poder manipularlos, debemos manipular la tabla que genera la insercion y actualizacion.

Para esto debemos hacer lo siguiente:

--declarar una valiable tabla

Declare @Id table (ID decimal(30,0));
Declare @PId int;


--Efectuar la inserción con el parámetro output indicar que campos se obtendrán como respuesta y con into hacia donde se enviaran


Insert INTO Base1 OUTPUT Inserted.Id into @Id values(getdate(),'CRIPTO','CRIPTOGRAFIA','MIGRACION',''Actualización');");

select top 1 @PId = ID from @OPId;");


La mano invisible controla el mundo

179. Obtener el Id insertado en una tabla de SQL.

Para obtener el Identity con el que se ha insertado un valor en una tabla de una base de datos de SQL Server, existen 2 formas, una es por medio de @@Identity

select @@Identity

este nos va a dar la ultima clave de identidad que se inserto en la base de datos, pero no siempre es preciso, si se ejecuta un trigger por ejemplo y este genera clave de identidad, esta función  nos entregara la ultima identidad, es decir la del trigger, ademas de que si como consecuencia de la acción efectuada se modifico algo mas tenemos que hacer una segunda consulta para determinarlo.

Entonces ¿Como obtenemos los valores?

La respuesta es sencilla usando el parámetro Outout en el Insert, por ejemplo

insert into tabla1 OUTPUT Inserted.id values (xxx,xx1....)

Como pueden ver estamos pidiendo el valor del campo insertado Inserted.id

Esta función es muy util, por que en un update, por ejemplo podemos obtener el valor del cambo original y del campo que se actualizo, por ejemplo.

Update tabla1 set campo1 = 'xxxx' OUTPUT Deleted.campo1 as valor_original,Inserted.campo1 as valor_nuevo


Esta instrucción nos permite ver los valores de entrada y salida de una actualización



La mano invisible controla el mundo

133. ¿Que esta haciendo mi servidor de SQL?

La salud de un servidor de SQL es muy importante, ya que una consulta muy pesada, un bloqueo, puede acabar con una aplicación completa, los contadores de performance de un servidor son una herramienta gratuita muy importante que nos permite ver prácticamente todo lo que hace un servidor, y estos pueden ser consultados por diversos métodos, vamos a revisar este tema mas adelante, si ya se que tengo varios temas abiertos pero es que sistemas es un mundo muy grande y si realmente quieren ser buenos arquitectos de sistemas deben conocer y ser expertos en todos los frentes, su labor es orquestar todos los recursos que tienen y aprovecharlos al maximo.

Hoy les dejo un query que les permite saber la actividad de su servidor de base de datos, para saber si un usuario esta abusando del servidor

SELECT es.session_id
    ,es.program_name
    ,es.login_name
    ,es.nt_user_name
    ,es.login_time
    ,es.host_name
    ,es.cpu_time
    ,es.total_scheduled_time
    ,es.total_elapsed_time
    ,es.memory_usage
    ,es.logical_reads
    ,es.reads
    ,es.writes
    ,st.text
FROM sys.dm_exec_sessions es
    LEFT JOIN sys.dm_exec_connections ec
        ON es.session_id = ec.session_id
    LEFT JOIN sys.dm_exec_requests er
        ON es.session_id = er.session_id
    OUTER APPLY sys.dm_exec_sql_text (er.sql_handle) st
WHERE es.session_id > 50   
ORDER BY es.cpu_time DESC

Que tengan felices lineas

77. sql EXEC master..xp_cmdshell ¿Usarlo?

Ya ha pasado un rato que no he escrito, una disculpa, múltiples actividades redujeron un poco mi tiempo, pero eso no quiere decir que me he olvidado de este proyecto.

La siguiente es una opinión mía, basada únicamente en mi experiencia es posible que no la compartan pero ese es el punto principal de la creación de un blog, incitar al debate.

En este caso hablaremos de xp_cmdshell, que nos sirve para hacer llamadas al sistema operativo desde un store procedure de la base de datos.

Si bien esto nos dota de una herramienta muy poderosa ya que nos permite la interacción con procesos externos, abre algunas puertas para poder ejecutar comando no deseados en el sistema operativo, y he aquí una de las grandes debilidades, si se tiene una mala codificación, este comando podría permitir una inyección de código directo al sistema operativo, que se ejecutaría con un usuario que tiene los privilegios del usuario de la base de datos, o del store procedure en cuestión.

Bueno esto siempre puede ser contenido, limitando los privilegios de acceso, evitando enviar concatenaciones a este comando que se puedan manipular desde afuera.

Sin embargo la puerta esta abierta.

Yo soy firme creyente de que el motor de base de datos no se debe sobrecargar, y sobrecargar el equipo en el que se encuentra dicho motor tampoco es buena idea, el uso de este comando hace que las acciones que se ejecutan sobre sistema operativo, o con un programa externo se ejecuten en el mismo equipo en el que se encuentra la base de datos, si hablo de una base de datos productiva en la que me interesa que el servidor en el que se encuentra la base se dedique solo a administrar la base, es un grave error quitarle recursos.

Los StorePorcedures no deben tener una lógica de negocio complicada, por que todo lo que se haga en ellos se hace en el servidor de base de datos, y repercute contra la cantidad de operaciones en paralelo que nuestro servidor sea capas de resolver, todos los accesos a base de datos deben ser rápidos, no deben tomar periodos de tiempo prolongados por que esto genera que las operaciones a la base se empiecen a encolar.

Cuando invocamos una llamada al sistema operativo desde el motor, este permanece a la espera de que el sistema operativo termine su proceso, empleando un hilo de la base de datos que se encuentra ocupado, pero sin una actividad real.

Una llamada al sistema operativo no puede ser cancelada desde la base de datos, imaginemos que hacemos una llamada a un proceso que consume gran cantidad de recursos, puede incluso provocar que nuestra base de datos se detenga, y aun si la cancelamos desde SQL, el proceso se cancelara hasta que la llamada al Sistema operativo termine, como consecuencia solo podremos cancelarlo desde el servidor mismo.

En mi opinión, usar una llamada a sistema operativo, desde la base de datos, si es posible pero siempre y cuando se valoren los pros y contras de la misma, yo en lo personal prefiero no usarla.

Felices lineas.

58. BCP (BulkCopy) Unicode en SQL Server(Ayuda Rápida XVIII)

Últimamente he estado hablando de muchos temas de SQL, no he olvidado los de C#, todavía debemos continuar con los patrones de diseño, y con los cambios que nos ha traído la nueva versión del lenguaje, pero como lo he mencionado anteriormente, un desarrollador completo debe conocer varios aspectos del entorno en el que se mueve.

El bulkcopy y el bulk insert son herramientas muy útiles por que nos permiten la rápida carga y descarga de datos, bulk insert es mucho mas flexible, y se puede considerar una evolución de Bulkcopy, hablemos hoy de bulkcopy.

Bulkcopy nos permite extraer a un archivo desde una ventana de comando toda la información o parte de ella de una tabla de SQL, de una manera muy rápida, pero ojo, la velocidad tiene un precio, Bulkcopy provoca bloqueos en la base de datos.

Así podemos extraer la información con una instrucción como esta:

bcp [Query] queryout [ruta] -S [Servidor] -T -d [Base_de_datos] -c -C ACP

que le estamos diciendo a la base que efectue un bulk copy de un query, esto lo copie a una ruta de salida, -S le indica cual es el nombre del servidor -T indica que no usara pasword, - d indica el catalogo -c indica el tipo de datos a usar en el archivo de texto - C ACP, este es el caso por el que escribimos este post, nos especifica la codificacion para que se guarden los datos que se encuentran en unicode de manera correcta.

¿Por que nos interesa esto?

Bueno el problema que se genera es que cuando usamos un tipo de dato como nvarchar, almacenamos la información en unicode, es decir tenemos un tamaño de palabra mayor que nos permite almacenar una mayor cantidad de símbolos, la version 5.1 de unicode permite alrededor de 100 000 símbolos, si no especificamos el formato, un símbolo se trata de representar por su equivalente en otro formato, como consecuencia el documento se corrompe.

En el caso del idioma español esto nos pasa con la ñ, los acentos que son propios de nuestro idioma.

Ahora si tenemos un archivo ¿Como podemos hacer el proceso contrario y subirlo a la base de datos?

La instrucción es muy similar

bcp [tabla] in [Archivo] -S [Servidor] -d [base de datos] -T -c -E -C ACP

que dice la instrucción, la instrucción anterior dice lo siguiente, inserta en la tabla, el contenido del archivo, -S nos indica el servidor de base de datos -d la base de datos -T que no pedirá password -c que los campos son caracteres -E como es una inserción le decimos que puede insertar columnas tipo identity -C ACP indica la codificación.

Es muy importante que si los datos se extraen con una codificación estos se importen con la misma codificación.


Felices lineas