SQL Server Snippets – Consultar roles y usuarios de una base de datos

El siguiente snippet sirve para obtener el listado de roles de una base de datos, con los usuarios que pertenecen a dicho rol:

SELECT
  p.name rol,
  p.principal_id id_rol,
  m.name usuario,
  m.principal_id id_usuario
FROM sys.database_role_members rm
  INNER JOIN sys.database_principals p
    ON rm.role_principal_id = p.principal_id
  INNER JOIN sys.database_principals m
    ON rm.member_principal_id = m.principal_id
ORDER BY p.name

La consulta devuelve un listado de roles y usuarios de la base de datos donde se ejecuta. Cabe mencionar que el listado contiene solamente los roles que están asignados a al menos un usuario (debido a los INNER JOIN), y que devuelve una fila por cada usuario y rol distinto. En este sentido, los usuarios que poseen más de un rol aparecerán repetidos en el listado. En otras palabras, si un usuario pertenece a N roles, entonces aparecerá repetido N veces en el listado, una vez por cada rol al que pertenece.

La consulta funciona (hasta donde yo he probado) en SQL Server 2005, 2008 y 2012.

Feliz día 🙂

Leer archivo de tabla de Visual Fox Pro (DBF) desde SQL Server

En mi trabajo, debido a que varios sistemas que se utilizan han sido desarrollados en Visual Fox, ha surgido la necesidad de leer los archivos de tabla (.DBF), desde SQL Server, para poder importar la información de los sistemas antiguos a los nuevos.

Existen varios driver para realizar esta tarea (dBase, ODBC, OleDB, etc.). En esta entrada me centraré en explicar como leer los archivos de tabla utilizando el driver de OleDB.

Primeramente, hay que instalar el driver OleDB en la computadora que alberga la instancia de SQL Server. El driver puede descargarse aquí. Si no me equivoco, no es necesario reiniciar la computadora ni los servicios de SQL Server luego de la instalación (puedo equivocarme, jejeje :)).

En segundo lugar, hay que asegurarse que el usuario que ejecuta la instancia de SQL Server, tenga permiso de lectura sobre el archivo de tabla DBF. Si se utiliza autenticación de Windows, entonces el usuario de Windows debe tener permiso de lectura sobre el archivo.

Finalmente, para leer el archivo, basta con hacer la lectura utilizando la función T-SQL llamada OPENROWSET, de la siguiente manera:

SELECT *  FROM OPENROWSET(
'VFPOLEDB.1',
'C:\ruta\del\archivo';'';'',
'SELECT * FROM NOMBRE_ARCHIVO')

Por ejemplo, para leer el archivo C:\Users\UsuarioPruebas\Documents\TABLA.dbf, la sentencia sería la siguiente:

SELECT *  FROM OPENROWSET(
'VFPOLEDB.1',
'C:\Users\UsuarioPruebas\Documents';'';'',
'SELECT * FROM TABLA')

El comando ha sido probado en SQL Server 2005, pero creería que también funciona para versiones posteriores (2008, 2012).

Feliz día 🙂

SQL Server Snippets – Aproximación de valor real a N-ésimo decimal superior/inferior

Supóngase que se tiene un valor real o float, que se quiere aproximar al N-ésimo decimal superior. Por ejemplo:


DECLARE @valor FLOAT
SET @valor = 1.9011

Para aproximar al 3er decimal superior, basta usar la función mátematica integrada de T-SQL llamada CEILING, de la siguiente forma:


SELECT CEILING(@valor*1000)/1000

El resultado de la operación es 1.902. Observemos paso a paso lo que sucede: Sigue leyendo

SQL Server Snippets – Columna de fecha de última modificación en SQL Server

Supongamos que por cuestiones de control de modificaciones y auditoría, se quiere contar con un campo de fecha de última modificación en todas las tablas de una base de datos de SQL Server.

El inconveniente es que SQL Server no posee nativamente un tipo de dato que guarde la fecha de última modificación de un registro, el cual se actualice automáticamente al modificar una fila de datos. Por ello, una alternativa a esto es definir una columna de tipo datetime en cada tabla que se actualice cada vez que se modifique uno o más registros de esta, a través de un trigger. Sigue leyendo

SQL Server Snippets – Buscar columna en base de datos

Saludos a todos. Aprovechando un poco mi hora de almuerzo (para escribir el post, aunque lo publiqué en la noche, jejeje), quería compartirles este pequeño snippet, que consiste en una consulta para obtener el listado de tablas en las que exista una determinada columna, y es básicamente el siguiente:

SELECT
  SCHEMA_NAME(schema_id) AS esquema,
  t.name AS tabla,
  c.name AS columna
FROM sys.tables AS t
  INNER JOIN sys.columns c ON t.object_id = c.object_id
WHERE c.name = 'nombre_columna'
ORDER BY esquema, tabla

Dónde nombre_columna es el nombre de la columna que se desea buscar. Sigue leyendo

SQL Server Snippets – Pausa

En esta ocasión les dejó un snippet corto, pero muy interesante, que consiste en hacer cómo hacer una pausa en SQL Server. Esto vendría siendo similar a un sleep en otros lenguajes de programación.

El comando es sencillo:

waitfor delay '00:01:00'

En dicho ejemplo se muestra el comando para hacer una pausa por un minuto. Sigue leyendo

Nueva sección: SQL Server Snippets

Minientrada

Saludos. En este día quiero anunciarles que he decidido iniciar una nueva sección, llamada SQL Server Snippets, con el objetivo de no dejar de lado el blog debido a la falta de tiempo, y compartir algunas de las cosas que aprendo día a día en mi trabajo con SQL Server. Esta sección consistirá básicamente en entradas breves con segmentos de código (snippets) generalmente cortos, pero muy útiles e interesantes.

Los invito a ver mi primera entrada de esta sección: Tablas con columna autonumérica

Feliz día.