TIPs de SQL

Este post se crea únicamente para tener a mano esas sentencias SQL que de vez en cuando se necesitan y que viene muy bién tener anotadas para utilizarlas cuando nos hace falta. A continuación se indican algunas de ellas.

Compactar una base de datos

BACKUP LOG BaseDatos WITH NO_LOG
BACKUP LOG BaseDatos WITH TRUNCATE_ONLY
DBCC SHRINKDATABASE (BaseDatos,0,TRUNCATEONLY)

También se puede utilizar:

BACKUP LOG BaseDatos WITH TRUNCATE_ONLY DBCC SHRINKDATABASE (BaseDatos,1)

Reiniciar campos autonumericos

DBCC CHECKIDENT ('tuTabla', RESEED, 1)

Programar una copia de seguridad
Meter como tarea programada lo siguiente en un .bat:

del "C:\ruta\BaseDatos.bak"
sqlcmd -S .\SQLEXPRESS -E -Q "BACKUP DATABASE BaseDatos TO DISK='C:\ruta\BaseDatos.bak'"

Esto puede conseguirse también desde SQL Management Express, el entorno gráfico para la gestión de la base de datos, bajo la opción Administración - Planes de mantenimiento, utilizando el asistente incluido, lo cual generará una sentencia como la siguiente:

BACKUP DATABASE [BaseDatos] TO  DISK = N'D:\BBDD\backup\BaseDatos.bak' WITH NOFORMAT, INIT,  NAME = N'BaseDatos_backup_2015_01_30_082549_1405325', SKIP, REWIND, NOUNLOAD,  STATS = 10

Además puede programarse para que realice este proceso como se haya decidido.

Se puede utilizar la siguiente sentencia para programa una copia que se sobreescriba cada vez:

BACKUP DATABASE [BaseDatos] TO  DISK = 'D:\ruta\BaseDatos.bak' WITH INIT

Consultar todas las tablas de la base de datos

select * from sys.tables

Consultar las tablas y la cantidad de registros de cada una de una base de datos

select substring(obj.name, 1, 50) as Tabla, ind.rows as Registros
from sysobjects as obj
inner join sysindexes as ind on obj.id = ind.id
where obj.xtype = 'u'
and ind.indid < 2

Consultar el tamaño en disco de una tabla de la base de datos

USE BaseDatos;
GO
EXEC sp_spaceused N'[Tabla]';
GO

Saber las conexiones abiertas con todas las bases de datos

SELECT DB_NAME(dbid) as 'Database Name',
COUNT(dbid) as 'Total Connections'
FROM master.dbo.sysprocesses WITH (nolock)
WHERE dbid > 0
GROUP BY dbid

Otra forma:

SELECT spid,STATUS,PROGRAM_NAME,LOGINAME=RTRIM(LOGINAME),HOSTNAME,CMD
FROM master.dbo.sysprocesses WITH (nolock)
WHERE dbid = (SELECT DB_ID('Tabla') as [Database ID])

Y otra:

Select *
From master..sysprocesses p,master..sysdatabases d
Where p.status != 'BACKGROUND' And d.dbid = p.dbid And d.name = 'BaseDatos'
Order by hostname

Cerrar conexion abierta (por spid)

dbcc inputbuffer (1018)
kill 1018

Obtener el esquema de una tabla con propiedades extendidas de columna

select table_name,ordinal_position,column_name,data_type,is_nullable,character_maximum_length
,(
SELECT value
FROM sys.extended_properties AS ep
  INNER JOIN sys.tables AS t ON ep.major_id = t.object_id
  INNER JOIN sys.columns AS c ON ep.major_id = c.object_id AND ep.minor_id = c.column_id
WHERE class=1 AND ep.name='MS_Description' AND t.name=ins.table_name AND c.name=ins.column_name
)  AS column_description
from information_schema.columns AS ins
where table_name = 'tuTabla'
order by ordinal_position

Búsqueda de huecos en campos autonuméricos

SELECT TOP(1) Id + 1 As PrimerHueco
FROM [Tabla] T
WHERE NOT EXISTS( SELECT * FROM [Tabla] WHERE Id = T.Id + 1)

Último día del mes actual

SELECT CONVERT(datetime,DATEADD(dd,-(DAY(DATEADD(mm,1,GETDATE()))),DATEADD(mm,1,GETDATE())),103)

Primer día del mes anterior

select DATEADD(month, DATEDIFF(month, 0, DATEADD(month, -1,GETDATE())), 0)