En Qué Filegroup se Localiza Cada Tabla ?

Amigos,

Quiero dejarles un script muy útil. El mismo sirve para obtener un recordset mediante el cual podrán tener un lista de todas las tablas correspondientes a una Base de Datos y su correspondiente FileGroup de alocación. Espero que les sea útil. Saludos.
------------------------------------------------------------------------------------------------------------------------------
-- Detalle: Este script lista todos las tablas de una BD y su correspondiente Filegroup --
-- Gustavo Herrera para Sql Server Tips - http://gherrerasqlserver.blogspot.com/ --
------------------------------------------------------------------------------------------------------------------------------
SELECT
o.[name] AS 'TABLE',
f.[name] AS 'FILE_GROUP'
FROM sys.indexes i
inner JOIN sys.filegroups f
ON i.data_space_id = f.data_space_id
INNER JOIN sys.objects o
ON i.[object_id] = o.[object_id]
WHERE
o.type = 'U' and
i.type < 2
Order by O.name
GO

Combinando Vistas del Sistema - Usando Metadata

Amigos,

Muchas veces solemos ahogarnos en un baso de agua al no utilizar todo el potencial que las vistas de catalogo del Sql Server gentilmente nos ofrece.

Aquí tiene un ejemplo de cómo listar cada uno de los índices de Base de Datos, mendiante la combinación de vistas del sistema.

Por favor, no duden en preguntar ante cualquier duda.

Con uds. es script.
---------------------------------------------------------------------------------------------------------------------
-- Detalle: Este script lista todos los índices de una Base de Datos --
-- Gustavo Herrera para Sql Server Tips - http://gherrerasqlserver.blogspot.com/ --
---------------------------------------------------------------------------------------------------------------------

SELECT
o.[name] AS 'Table Name',
i.[name] as 'Index Name',
i.[type_desc]'Description',
f.[name]AS 'Filegroup',
c.[name] as 'Fields'
FROM sys.indexes i
inner JOIN sys.filegroups f -- para saber el filegroup
ON i.data_space_id = f.data_space_id
INNER JOIN sys.objects o -- para nombre de la tabla
ON i.[object_id] = o.[object_id]
inner join Sys.Index_Columns as z --
on z.[object_id] = o.[object_id]
INNER JOIN sys.columns c
on c.[object_id] = z.[object_id] and
c.Column_ID = z.Column_ID
WHERE
o.type = 'U'
and i.[type_desc] <> 'HEAP'
and i.[type_desc] <> 'CLUSTERED' and
i.index_Id = z.index_id
order by o.name,
i.name
GO

Listar Todos Los Indices Existentes en una Base de Datos con un Solo Script

Amigos,

Hay muchas maneras de tener una mirada de los índices de una tabla, pero ninguna de ellas permite facilmente listar Todos los Indices de una Base de Datos en un sólo paso.

Ejemplos:

- Usando el Enterprise Manager.., sólo podremos obtener la visión de una tabla y un índice al mismo tiempo

- Usando el SP_Helpindex..., que sólo nos permite listar todos los índices de a una tabla por vez..

- Utilizando la tabla sysindixes, con lo cual tendremos que escribir complejas sentencias para lograr algo útil.

Pero no desesperen, aquí les traigo la solución. Espero les sea de utilidad, Saludos.
---------------------------------------------------------------------------------------------------------------------
-- Detalle: Este script lista todos los índices de una Base de Datos --
-- Gustavo Herrera para Sql Server Tips - http://gherrerasqlserver.blogspot.com/ --
---------------------------------------------------------------------------------------------------------------------

drop table #spindtab
set nocount on
declare @objname nvarchar(776), -- si quiero puedo ingresar el nombre de la tabla por parámetro
@objid int,
@indid smallint,
@groupid smallint,
@indname sysname,
@groupname sysname,
@status int,
@keys nvarchar(2126),
@dbname sysname,
@usrname sysname

-- Chequear para asegurarse que el nombre de la tabla entrado por parámetro pertenezca a la base de datos --
select @dbname = parsename(@objname,3)

if @dbname is not null and @dbname <> db_name()
begin
raiserror(15250,-1,-1)

end


-- Creación Tabla Temporal
create table #spindtab
(
usr_name sysname null,
table_name sysname null,
index_name sysname collate database_default null,
stats int null,
groupname sysname collate database_default null ,
index_keys nvarchar(2126) collate database_default null -- see @keys above for length descr
)


-- Se Guarda en un Curso el Id, Nombre de de la Tabla y Owner
declare ms_crs_tab cursor local static for
select
sysobjects.id,
sysobjects.name,
sysusers.name
from sysobjects
inner join sysusers
on sysobjects.uid = sysusers.uid
where type = 'U'

open ms_crs_tab
fetch ms_crs_tab
into @objid, @objname, @usrname

while @@fetch_status >= 0
Begin

-- Se consulta la tabla de índices.
declare ms_crs_ind cursor local static for
select
indid,
groupid,
name,
status
from sysindexes
where id = @objid and
indid between 1 and 254 and
(status & 64)=0
order by indid
open ms_crs_ind
fetch ms_crs_ind into
@indid,
@groupid,
@indname,
@status

-- Ahora se chequea cada índice, comprendiendo el tipo y el campo que utiliza, guardando la info
-- en una tabla temporal que será impresa al final

while @@fetch_status >= 0
begin
-- Primer vamos a entender cuales son las columnas involucradas
declare
@i int,
@thiskey nvarchar(131)

select @keys = index_col(@usrname + '.' + @objname, @indid, 1), @i = 2
if (indexkey_property(@objid, @indid, 1, 'isdescending') = 1)
select @keys = @keys + '(-)'

select @thiskey = index_col(@usrname + '.' + @objname, @indid, @i)
if ((@thiskey is not null) and (indexkey_property(@objid, @indid, @i, 'isdescending') = 1))
select @thiskey = @thiskey + '(-)'

while (@thiskey is not null )
begin
select @keys = @keys + ', ' + @thiskey, @i = @i + 1
select @thiskey = index_col(@usrname + '.' + @objname, @indid, @i)
if ((@thiskey is not null) and (indexkey_property(@objid, @indid, @i, 'isdescending') = 1))
select @thiskey = @thiskey + '(-)'
end

select @groupname = groupname from sysfilegroups where groupid = @groupid

-- Insertar una columna para el índice relevado
insert into #spindtab values (@usrname, @objname, @indname, @status, @groupname, @keys)

-- Vamos por el próximo índice
fetch ms_crs_ind into @indid, @groupid, @indname, @status
end
deallocate ms_crs_ind

-- Vamos a buscar otra tabla
fetch ms_crs_tab into @objid, @objname, @usrname
end
deallocate ms_crs_tab

-- SET UP SOME CONSTANT VALUES FOR OUTPUT QUERY
declare @empty varchar(1) select @empty = ''
declare @des1 varchar(35),
@des2 varchar(35),
@des4 varchar(35),
@des32 varchar(35),
@des64 varchar(35),
@des2048 varchar(35),
@des4096 varchar(35),
@des8388608 varchar(35),
@des16777216 varchar(35)
select @des1 = name from master.dbo.spt_values where type = 'I' and number = 1
select @des2 = name from master.dbo.spt_values where type = 'I' and number = 2
select @des4 = name from master.dbo.spt_values where type = 'I' and number = 4
select @des32 = name from master.dbo.spt_values where type = 'I' and number = 32
select @des64 = name from master.dbo.spt_values where type = 'I' and number = 64
select @des2048 = name from master.dbo.spt_values where type = 'I' and number = 2048
select @des4096 = name from master.dbo.spt_values where type = 'I' and number = 4096
select @des8388608 = name from master.dbo.spt_values where type = 'I' and number = 8388608
select @des16777216 = name from master.dbo.spt_values where type = 'I' and number = 16777216

-- DISPLAY THE RESULTS
select
'usr_name'=usr_name,
'table_name'=table_name,
'index_name' = index_name,
'index_description' = convert(varchar(210), --bits 16 off, 1, 2, 16777216 on, located on group
case when (stats & 16)<>0 then 'clustered' else 'nonclustered' end
+ case when (stats & 1)<>0 then ', '+@des1 else @empty end
+ case when (stats & 2)<>0 then ', '+@des2 else @empty end
+ case when (stats & 4)<>0 then ', '+@des4 else @empty end
+ case when (stats & 64)<>0 then ', '+@des64 else case when (stats & 32)<>0 then ', '+@des32 else @empty end end
+ case when (stats & 2048)<>0 then ', '+@des2048 else @empty end
+ case when (stats & 4096)<>0 then ', '+@des4096 else @empty end
+ case when (stats & 8388608)<>0 then ', '+@des8388608 else @empty end
+ case when (stats & 16777216)<>0 then ', '+@des16777216 else @empty end
+ ' located on ' + groupname),
'index_keys' = index_keys
from #spindtab
order by table_name, index_name

GO

drop table #spindtab

Deshabilitar/Habilitar todos los Foreign Key de una Base de Datos

Amigos,

Como uds saben ante operaciones tales como el borrado de datos de tablas referenciadas por FK, es necesario desahbilitar los mismos, para luego, una vez borrada la data, volver a habilitarlos.

Les dejo aquí un script que permite automatizar la tarea, espero que les sea de utilidad.

--------------------------------------------------------------------------------------------------------------------
-- Detalle: Este script permite deshabilitar/habilitar todos los FK de de una BD --
-- Gustavo Herrera para Sql Server Tips - http://gherrerasqlserver.blogspot.com/ --
---------------------------------------------------------------------------------------------------------------------

DECLARE
@TableName varchar(255),
@sql varchar(4000)

DECLARE cTable CURSOR FORWARD_ONLY FOR

-- Obtengo los nombres de las tablas de las bases de datos.
SELECT name
FROM sys.objects
WHERE
type = 'U'

OPEN cTable
FETCH NEXT FROM cTable
INTO @TableName

WHILE @@FETCH_STATUS = 0
BEGIN
-- Para deshabilitar
SET @sql = 'alter table [' + @TableName + '] nocheck constraint all'

-- Para volver a habilitar
SET @sql = 'alter table ' + @TableName + ' check constraint all '

EXEC (@sql)

FETCH NEXT FROM cTable INTO @TableName
END

CLOSE cTable
DEALLOCATE cTable

Conocer Todos los Foreign Key de una Base de Datos.

Amigos,

Les dejo un script que les permitirá conocer todos los Foreign Key de todas las tablas de una Base de Datos.

Es un script muy útil a la hora de encarar un proceso de migración, borrado, update, inserción de datos etc en cualquier tabla.

La idea es no tener que recorrer por consola una a una las tablas y poder solucionar esto rápidamente.

Va el script:

-------------------------------------------------------------------------------------
-- Detalle: Este script permite listar todos los FK de todas las tablas de una BD --
-- Gustavo Herrera para Sql Server Tips - http://gherrerasqlserver.blogspot.com/ --
-- ----------------------------------------------------------------------------------

SELECT
Origin_Table = FK.TABLE_NAME,
Constraint_Name = C.CONSTRAINT_NAME,
FK_Column = CU.COLUMN_NAME,
Reference_Table = PK.TABLE_NAME,
Reference_Column = PT.COLUMN_NAME
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK ON C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK ON C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU ON C.CONSTRAINT_NAME = CU.CONSTRAINT_NAME
INNER JOIN (
SELECT i1.TABLE_NAME, i2.COLUMN_NAME
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS i1
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE i2 ON i1.CONSTRAINT_NAME = i2.CONSTRAINT_NAME
WHERE i1.CONSTRAINT_TYPE = 'PRIMARY KEY'
) PT ON PT.TABLE_NAME = PK.TABLE_NAME
ORDER BY
origin_table