Listar Tabelas e informações

Listas tabelas, linhas e tamanho em disco.

declare @vname sysname
declare @tmpTamTabela table (
name sysname null
, rows int null
, reserved varchar(25) null
, data varchar(25) null
, index_size varchar(25) null
, unused varchar(25) null )

declare cp1 cursor local fast_forward read_only for
select name
from sysobjects
where type = ‘U’
order by name

open cp1

while 1 = 1
begin
fetch next from cp1 into @vname
if @@fetch_status 0 break

insert into @tmpTamTabela (name, rows, reserved
, data, index_size, unused)
exec sp_spaceused @vname

end
close cp1
deallocate cp1

select name as ‘Nome’
, rows as ‘Linhas’
, convert(int, replace(reserved, ‘ KB’,”))/1024 as ‘Tamanho total MB’
, convert(int, replace(data, ‘ KB’,”))as ‘Dados KB’
, convert(int, replace(index_size, ‘ KB’,”))as ‘Index KB’
, convert(int, replace(unused, ‘ KB’,”))as ‘Não utilizado KB’
from @tmpTamTabela
order by convert(int, replace(reserved, ‘ KB’,”)) desc

Modificando Collate SQL Server

— VERIFICANDO COLLACTION DAS TABELAS
SELECT
a.name as ‘NomeTabela’ ,
b.name as ‘NomeColuna’,
b.collation ,
c.name as ‘TipoDados’,
b.length as ‘Tamanho’
FROM sysobjects A
inner join syscolumns B on a.id = b.id
inner join sys.types C on C.system_type_id = b.xtype
WHERE a.xType = ‘U’
and b.collation is not null
and ((c.name = ‘char’) or (c.name = ‘varchar’))
and a.name in (SELECT table_name FROM information_schema.tables)
order by a.name;

— SQL SERVER 2000

— VERIFICAR COLLATION DE UMA OU TODAS AS TABELAS

SELECT
a.name as ‘NomeTabela’ ,
b.name as ‘NomeColuna’,
b.collation ,
c.name as ‘TipoDados’,
b.length as ‘Tamanho’
FROM sysobjects A
inner join syscolumns B on a.id = b.id
inner join systypes C on C.xtype = b.xtype
WHERE a.xType = ‘U’
and b.collation is not null
and ((c.name = ‘char’) or (c.name = ‘varchar’))
and a.name in (SELECT table_name FROM information_schema.tables)
AND a.name LIKE ‘t’– FILTRO NOME DA TABELA
order by a.name

— VERIFICAR COLLATION DE UMA OU TODAS AS TABELAS

SELECT
a.name as ‘NomeTabela’ ,
b.name as ‘NomeColuna’,
b.collation ,
c.name as ‘TipoDados’,
b.length as ‘Tamanho’
FROM sysobjects A
inner join syscolumns B on a.id = b.id
inner join systypes C on C.xtype = b.xtype
WHERE a.xType = ‘U’
and b.collation is not null
and ((c.name = ‘char’) or (c.name = ‘varchar’))
and a.name in (SELECT table_name FROM information_schema.tables)
AND a.name LIKE ‘t’– FILTRO
order by a.name

— ALTERAR
DECLARE @tabela VARCHAR(100)
DECLARE @collate VARCHAR(100)
–SET @tabela=’t’
SET @collate=’SQL_Latin1_General_CP1_CI_AI’ — SQL_Latin1_General_CP1_CI_AI
SELECT ‘Alter Table ‘+ a.name + ‘ Alter Column ‘+ b.name +’ ‘+c.name +’ (‘+CONVERT(VARCHAR(50),b.length)+’) Collate’+’ ‘+@collate
FROM sysobjects A
inner join syscolumns B on a.id = b.id
inner join systypes C on C.xtype = b.xtype
WHERE a.xType = ‘U’
and b.collation is not null
and ((c.name = ‘char’) or (c.name = ‘varchar’))
and a.name in (SELECT table_name FROM information_schema.tables)
and b.collation not like ‘SQL_Latin1_General_CP1_CI_AI’
–AND a.name=@tabela — FILTRO
order by a.name

— COMPROVANDO QUE NÃO EXISTEM TABELAS COM COLLATION DIFERENTE DO SOLICITADO 

SELECT
a.name as ‘NomeTabela’ ,
b.name as ‘NomeColuna’,
b.collation ,
c.name as ‘TipoDados’,
b.length as ‘Tamanho’
FROM sysobjects A
inner join syscolumns B on a.id = b.id
inner join sys.types C on C.system_type_id = b.xtype
WHERE a.xType = ‘U’
and b.collation is not null
and ((c.name = ‘char’) or (c.name = ‘varchar’))
and a.name in (SELECT table_name FROM information_schema.tables)
and b.collation !=’SQL_Latin1_General_CP1_CI_AI’
order by a.name

Ajustar logins orfãos

Segue procedure para efetuar os ajustes.

create procedure [dbo].[SP_AJUSTA_LOGINS] @db varchar(30) = NULL
as
SET NOCOUNT ON
DECLARE @sql nvarchar(1000)
DECLARE @User sysname
–Temp com os usuarios
CREATE TABLE #tbUsuarios (usuarios sysname,id BIGINT)
IF @db is null
begin
print ‘Por segurança, o parametro DataBase é obrigatório. Para executar para todos os DBs utilize sp_ajusta_logins ”TODOS” ou sp_ajusta_logins ”nome_da_base”’
end else
IF not(upper(Rtrim(lTrim(@db))) = ‘TODOS’) — Para um db específico
BEGIN
PRINT ‘Associando usuários órfãos para o DataBase ‘+@Db+’ – ‘+convert(varchar,getdate())
SET @sql = @db+’.dbo.sp_change_users_login ”report”’
INSERT INTO #tbUsuarios exec sp_executesql @sql

IF exists(SELECT usuarios FROM #tbUsuarios)
BEGIN
SELECT @User = min(usuarios) from #tbUsuarios
WHILE @User is not null
BEGIN
SELECT @sql = @db+’.dbo.sp_change_users_login ”Update_One”,”’+ @User + ”’,”’+ @User +””
begin try
EXEC sp_executesql @sql
SET @sql = ‘O usuário ”’+ @User +”’ do database ”’+@db +”’ foi associado ao seu login ”’+@User+””
Print @sql
end try
begin catch
SET @sql = N’Ocorreu um erro ao atualizar o usuário ”’+ @User +”’ do database ”’+@db+”’ – ‘+ERROR_MESSAGE()
RAISERROR (@sql , 10, 1);
end catch
SELECT @User = min(usuarios) from #tbUsuarios where usuarios > @User
END
END
END
ELSE
BEGIN
print ‘Executando SP para todos os DBS

— Pesquisa em todos os dbs
SELECT @db = min(name) from master.dbo.sysdatabases where name not in (‘tempdb’, ‘pubs’, ‘msdb’, ‘NorthWind’, ‘master’,’model’)
PRINT ‘Associando usuários órfãos para o DataBase ‘+@Db+’ – ‘+convert(varchar,getdate())
WHILE @db is not null
BEGIN
SET @sql = @db+’.dbo.sp_change_users_login ”report”’

INSERT INTO #tbUsuarios exec sp_executesql @sql
IF exists(SELECT usuarios FROM #tbUsuarios)
BEGIN
SELECT @User = min(usuarios) from #tbUsuarios
WHILE @User is not null
BEGIN
SET @sql = @db+’..sp_change_users_login ”Update_One”,”’+ @User + ”’,”’+ @User +””
begin try
EXEC sp_executesql @sql
SET @sql = ‘O usuário ”’+ @User +”’ do database ”’+@db +”’ foi associado ao seu login ”’+@User+””
Print @sql
end try
begin catch
SET @sql = N’Ocorreu um erro ao atualizar o usuário ”’+ @User +”’ do database ”’+@db+”’ – ‘+ERROR_MESSAGE()
RAISERROR (@sql , 10, 1);
end catch
SELECT @User = min(usuarios) from #tbUsuarios where usuarios > @User
END
END
DELETE #tbUsuarios
SELECT @db = min(name) FROM master.dbo.sysdatabases WHERE name not in (‘tempdb’, ‘pubs’, ‘msdb’, ‘NorthWind’, ‘master’,’model’)
AND Name > @db
END
END

DROP TABLE #tbUsuarios
print ‘Processo Concluído!’