Thursday, July 31, 2014

Fix orphan for all the database on a single shot.

Please change the RESULTS TO TEXT GRID and run the below query. Results given by this query need to run in Query

       EXECUTE master.sys.sp_MSforeachdb ' USE [?];
                select ''EXEC ?.dbo.sp_change_users_login '''''' +  ''update_one'' + '''''''' +  '',['' + '''' +  name + ''''+ ''],['' +'''' +   name + '']'' + + ''''
                from sysusers
       where sid NOT IN (select sid from master..syslogins )
       AND islogin = 1 AND name NOT LIKE ''%guest%''
       '

      
       EXECUTE master.sys.sp_MSforeachdb ' USE [?];
                select ''EXEC ?.dbo.sp_change_users_login '''''' +  ''update_one'' + '''''''' +  '','' + '''''''' +  name + ''''''''
                     + '','' + '''''''' +   name + '''''''' 
                from sysusers
       where sid NOT IN (select sid from master..syslogins )
       AND islogin = 1 AND name NOT LIKE ''%guest%''
       '




Friday, July 11, 2014

Scripting Server Permissions And Role Assignments


SET NOCOUNT ON

SELECT  
'USE' SPACE(1) + QUOTENAME('MASTER'AS '--Database Context'

-- Role Members 

SELECT  'EXEC sp_addsrvrolemember @rolename =' SPACE(1)
        + 
QUOTENAME(usr1.name'''') + ', @loginame =' SPACE(1)
        + 
QUOTENAME(usr2.name''''AS '--Role Memberships' FROM    sys.server_principals AS usr1
        
INNER JOIN sys.server_role_members AS rm ON usr1.principal_id rm.role_principal_id
        
INNER JOIN sys.server_principals AS usr2 ON rm.member_principal_id usr2.principal_id ORDER BY rm.role_principal_id ASC

-- Permissions 

SELECT  server_permissions.state_desc COLLATE SQL_Latin1_General_CP1_CI_AS
        
' ' server_permissions.permission_name COLLATE SQL_Latin1_General_CP1_CI_AS
        
' TO [' server_principals.name COLLATE SQL_Latin1_General_CP1_CI_AS
        
']' AS '--Server Level Permissions' FROM    sys.server_permissions AS server_permissions WITH NOLOCK )
        
INNER JOIN sys.server_principals AS server_principals WITH NOLOCK ONserver_permissions.grantee_principal_id server_principals.principal_id WHERE   server_principals.type IN 'S''U''G' ORDER BY server_principals.name,
        
server_permissions.state_desc,
        
server_permissions.permission_name 

List databases from SQL Server 2000

with below query you can List the databases from SQL Server 2000


SELECT CATALOG_NAME 
    FROM INFORMATION_SCHEMA.SCHEMATA 
    ORDER BY CATALOG_NAME


Monday, July 7, 2014

DBCC IND

DBCC IND 

DBCC IND command provides the list of pages used by the table or index. The command provides the page numbers used by the table along with previous page number,next page number. The command takes three parameters.

Syntax is provided below.

DBCC ind ( <database_name>, <table_name>, non clustered index_id*) 


The third parameter can either be a Non Clustered index id ( provided by sys.indexes ) or 1,0,-1,-2. -1 provides complete information about all type of pages( in row data,row over flow data,IAM,all indexes ) associated with the table. The list of columns returned are provided below.

IndexID: Provides id of the index. 0 - for heap, 1 - clustered index.,Non 
clustered ids > 2 .
PagePID : Page number
IAMFID : Fileid of the file containing the page ( refer sysfiles ) 
ObjectID : Objectid of the table used.
Iam_chain_type: Type of data stored ( in row data,row overflow etc )
PageType : 1 refers to Data page, 2 -> Index page,3 and 4 -> text pages
Indexlevel: 0 - refers to leaf. Highest value refers to root of an index.
NextPagePID,PrevPagePID : refers to next and previous page numbers.

List all indexes in a table

exec sp_helpindex 'TABLE_NAME'  -- TABLE_NAME is a table name


Script to list all NON-Clustered indexes in a database

select t.name as tablename, i.* 
from sys.indexes i, sys.tables t
where i.object_id = t.object_id
  and i.type_desc = 'NONCLUSTERED'

Script to get all indexes list in a database

declare @EmptyString varchar(1)
select @EmptyString = ''

-- 35 is the lenght of the name field of the master.dbo.spt_values table
declare @IgnoreDuplicateKeys varchar(35),
    @Unique varchar(35),
    @IgnoreDuplicateRows varchar(35),
    @Clustered varchar(35),
    @Hypotethical varchar(35),
    @Statistics varchar(35),
    @PrimaryKey varchar(35),
    @UniqueKey varchar(35),
    @AutoCreate varchar(35),
    @StatsNoRecompute varchar(35)

select @IgnoreDuplicateKeys = name from master.dbo.spt_values 
    where type = 'I' and number = 1 --ignore duplicate keys
select @Unique = name from master.dbo.spt_values 
    where type = 'I' and number = 2 --unique
select @IgnoreDuplicateRows = name from master.dbo.spt_values 
    where type = 'I' and number = 4 --ignore duplicate rows
select @Clustered = name from master.dbo.spt_values 
    where type = 'I' and number = 16 --clustered
select @Hypotethical = name from master.dbo.spt_values 
    where type = 'I' and number = 32 --hypotethical
select @Statistics = name from master.dbo.spt_values 
    where type = 'I' and number = 64 --statistics
select @PrimaryKey = name from master.dbo.spt_values 
    where type = 'I' and number = 2048 --primary key
select @UniqueKey = name from master.dbo.spt_values 
    where type = 'I' and number = 4096 --unique key
select @AutoCreate = name from master.dbo.spt_values 
    where type = 'I' and number = 8388608 --auto create
select @StatsNoRecompute = name from master.dbo.spt_values 
    where type = 'I' and number = 16777216 --stats no recompute
select o.name,
  i.name,
  'index description' = convert(varchar(210), --bits 16 off, 1, 2, 16777216 on
      case when (i.status & 16)<>0 then @Clustered else 'non'+@Clustered end
      + case when (i.status & 1)<>0 then ', '+@IgnoreDuplicateKeys else @EmptyString end
      + case when (i.status & 2)<>0 then ', '+@Unique else @EmptyString end
      + case when (i.status & 4)<>0 then ', '+@IgnoreDuplicateRows else @EmptyString end
      + case when (i.status & 64)<>0 then ', '+@Statistics else
      case when (i.status & 32)<>0 then ', '+@Hypotethical else @EmptyString end end
      + case when (i.status & 2048)<>0 then ', '+@PrimaryKey else @EmptyString end
      + case when (i.status & 4096)<>0 then ', '+@UniqueKey else @EmptyString end
      + case when (i.status & 8388608)<>0 then ', '+@AutoCreate else @EmptyString end
      + case when (i.status & 16777216)<>0 then ', '+@StatsNoRecompute else @EmptyString end),
  'index column 1' = index_col(o.name,indid, 1),
  'index column 2' = index_col(o.name,indid, 2),
  'index column 3' = index_col(o.name,indid, 3)
from sysindexes i, sysobjects o
where i.id = o.id and
      indid > 0 and indid < 255 --all the clustered (=1), non clusterd (>1 and <251), and text or image (=255) 
      and o.type = 'U' --user table
      --ignore the indexes for the autostat
      and (i.status & 64) = 0 --index with duplicates
      and (i.status & 8388608) = 0 --auto created index
      and (i.status & 16777216)= 0 --stats no recompute
      order by o.name