SQL Server Tips

How to list all objects and indexes [SQL Server]

Today I had to list which objects belongs to which filegroup.
It’s a simple and useful script that you can take from system catalogues: sys.filegroups, sys.indexes, sys.database_files and sys.data_spaces.

SELECT Object_name(i.[object_id]) AS [ObjectName],
       i.[index_id]               AS [IndexID],
       i.[name]                   AS [IndexName],
       i.[type_desc]              AS [IndexType],
       f.[name]                   AS [FileGroup],
       d.[physical_name]          AS [DatabaseFileName]
FROM   [sys].[indexes] i
       LEFT JOIN sys.partition_schemes pf
              ON pf.[data_space_id] = i.[data_space_id]
       LEFT JOIN [sys].[filegroups] f
              ON f.[data_space_id] = i.[data_space_id]
       LEFT JOIN [sys].[database_files] d
              ON f.[data_space_id] = d.[data_space_id]
       LEFT JOIN [sys].[data_spaces] s
              ON f.[data_space_id] = s.[data_space_id]
WHERE  Objectproperty(i.[object_id], 'IsUserTable') = 1
ORDER  BY Object_name(i.[object_id]),
          f.[name],
          i.[data_space_id]

Take a look this sample

imagem

In the next post I’m going to share How Move Index to other FileGroup.

Advertisements

Repository of White Papers – SQL Server

Like a good reader,
I highly recommend that you read these white papers.
These links contain much more knowledge that I can provide here.

https://www.sqlskills.com/sql-server-resources/sql-server-whitepapers/
http://social.technet.microsoft.com/wiki/contents/articles/13146.white-paper-gallery-for-sql-server.aspx

That’s it! Have fun.


Lookup All File Sizes, Status, and FileName for all Databases

This script can help you find some details in all databases.

USE MSDB
go
IF EXISTS(SELECT NULL FROM msdb..sysobjects where name = 'DBFileSizes' AND type ='U')
     TRUNCATE TABLE dbo.DBFileSizes
ELSE
CREATE TABLE dbo.DBFileSizes(
     [DBname] nvarchar(128) NULL,
     [fileid] [smallint] NULL,
     [groupid] [smallint] NULL,
     [size] [int] NOT NULL,
     [maxsize] [int] NOT NULL,
     [growth] [int] NOT NULL,
     [status] [int] NULL,
     [perf] [int] NULL,
     [name] [sysname] NOT NULL,
     [filename] [nvarchar](260) NOT NULL )
EXEC master..sp_msforeachdb "INSERT INTO msdb.dbo.DBFileSizes
SELECT '?' AS
[DBname], * FROM [?]..[sysfiles]"
SELECT * 
     FROM dbo.DBFileSizes
DROP TABLE dbo.DBFileSizes

imagem

How do I find a value anywhere in a SQL Server Database

The query below will search all fields in all tables for a specific value.

--Variable Delaration
Declare @keytosearch varchar(max), @Database_Selected varchar(50)

set @keytosearch ='%danilo%'
set @Database_Selected= 'Northwind'

Declare @Table varchar(100), @Table_Name Cursor, @Count_Column int, @Result nvarchar(4000),@ID int,@ID_inserted int,@Count_Table int, @data_type varchar(10)

set @ID_inserted=0  
set @Count_Table=0

DECLARE @column varchar(max), @Column_Name CURSOR
--Variable Delaration end

--Second Cursor start
declare @informationName varchar(50), @SysName varchar(50), @Var varchar(5)

set @informationName=@Database_Selected+'.'+'information_schema.COLUMNS'
Set @SysName=@Database_Selected+'.'+'sys.objects'
Set @Var='u'

--Database Selected start
Create Table #SearchResult(ID int identity(1,1) not null, TableName varchar(50), ColumnName varchar(50))
Create Table #SearchTestResult(ID int identity(1,1) not null, ColumnName varchar(max))
Create Table #Table_Name(ID int identity(1,1) not null, table_name varchar(50))

IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE='BASE TABLE' AND TABLE_NAME='Column_Nam')
  drop table Column_Nam

insert into #Table_Name exec ('Select name from '+@SysName +' where type= '''+@Var+'''')

--First Cursor open
SET @Table_Name = CURSOR FOR Select table_name from #Table_Name

open @Table_Name

Fetch Next from @Table_Name into @Table

WHILE @@FETCH_STATUS = 0
BEGIN
  set @Count_Table =@Count_Table+1
  --Second cursor opened
  --print 'Select column_name from  '+@informationName +' where table_name= '''+@Table+''''s
  Exec('SELECT column_name,data_type INTO Column_Nam FROM  '+ @informationName +' where table_name = '''+@Table+'''')
  SET @Column_Name = CURSOR FOR (select column_name from Column_Nam )

  OPEN @Column_Name FETCH NEXT FROM @Column_Name INTO @column

  set @Table=@Database_Selected+'.[dbo].['+@Table+']'
  WHILE @@FETCH_STATUS = 0
   BEGIN
    set @data_type= (SELECT top 1 data_type FROM Column_Nam Where column_name= @column)

    if @data_type is not null and (@data_type='varchar' or @data_type='nvarchar' or @data_type='Text')
     begin
      set @Result=null
      if @column like '%-%' begin  set @Result =('SELECT ''' + @column +''' FROM ' + @Table 
                +' Where ''' + @column + ''' Like '''+@keytosearch+'''') end
        else
          set @Result =('SELECT ' + @column +' FROM ' + @Table  +' Where ' + @column + ' Like '''+@keytosearch+'''')

      insert into #SearchTestResult exec(@Result)

      set @ID=0
      set @ID=(Select ID from #SearchTestResult where ID=Scope_identity())

       if @ID is not null
        begin
        set @ID_inserted=(select top 1 ID from #SearchResult order by ID desc)
            if @ID_inserted = @ID
             begin
              print ''
             end
           else 
             insert into #SearchResult values (@Table,@column)
       end 
    end
    FETCH NEXT FROM @Column_Name INTO @column
   END 
  CLOSE @Column_Name 
  DEALLOCATE @Column_Name
  --Second cursor closed

  drop table Column_Nam

  Fetch Next from @Table_Name into @Table
End

close @Table_Name 

Deallocate @Table_Name
--First Cursor Closed

Select * from #SearchResult

Enjoy it!

High Availability and Disaster Recover – Syncronize logins [SQL Server]

Often I connect in new environments and I see solutions of High Availability and Disaster Recovery. But just the simple solution is sufficient for your applications?
I’m saying that because in many cases a just simple detail is forgotten. Many professionals forget to create a option to syncronize logins and when you need to use the otherside, application not run and your solution of High Availability failed.

Follow below one example of script to syncronize your environment.

You need to create one LINKEDSERVER between instances and run one job for a specific times or a trigger to execute this jobs when you have a new user or when the user has been modified.

After you need to create a procedure hexadecimal according with your version. Look that and create in both instances: http://support.microsoft.com/kb/918992

And finaly, create the procedure below. Don’t forget to change the name of LINKEDSERVER

This script is simple and just create login without administratives privileges and with the same SID and when password has been modified. If you need sysadmin for example, you will need input manually. I think is better because sometimes you have a diferent instance and maybe you can’t grant acess in a especific server.

BEGIN
SET NOCOUNT ON

	CREATE TABLE #Logins
	(
		loginId int IDENTITY(1, 1) NOT NULL,
		loginName nvarchar(128) NOT NULL,
		passwordHash varbinary(256) NULL,
		default_database_name nvarchar(128) NULL,
		sid varbinary(85) NOT NULL
	)

	INSERT INTO #Logins(loginName, passwordHash, sid, default_database_name)
	SELECT *
	FROM OPENQUERY([INSTANCE], '
	SELECT name, CONVERT(varbinary(256), LOGINPROPERTY(name, ''PasswordHash'')), sid, default_database_name
	FROM master.sys.server_principals
	WHERE
		type = ''S'' AND 
		name NOT IN (''sa'', ''guest'',''##MS_PolicyEventProcessingLogin##'',''usr_alwayson'') AND
		create_date >= ''01/05/2014''
	ORDER BY name')

	DECLARE 
		@count int, @loginId int, @loginName nvarchar(128), 
		@passwordHashOld varbinary(256), @passwordHashNew varbinary(256), 
		@SID_varbinary varbinary(85), @sql nvarchar(4000), @password varchar(514), @default_database_name nvarchar(128), @SID_string varchar (514)

	SELECT @loginId = 1, @count = COUNT(*)
	FROM #Logins

	WHILE @loginId <= @count
	BEGIN
		SELECT @loginName = loginName, @passwordHashNew = passwordHash, @SID_varbinary = sid, @default_database_name = default_database_name
		FROM #Logins
		WHERE loginId = @loginId

		IF NOT EXISTS (SELECT * FROM master.sys.server_principals WHERE name = @loginName)
		BEGIN
			EXEC master.dbo.sp_hexadecimal @passwordHashNew, @password OUTPUT
			EXEC master.dbo.sp_hexadecimal @SID_varbinary,@SID_string OUT

			SET @sql = 'CREATE LOGIN ' + @loginName + ' WITH PASSWORD = '
			SET @sql = @sql + CONVERT(nvarchar(512), COALESCE(@password, 'NULL')) 
			SET @sql = @sql + ' HASHED , SID = ' + CONVERT(nvarchar(512), @SID_string)
			SET @sql = @sql + ' , DEFAULT_DATABASE = [' + @default_database_name + ']'
			SET @sql = @sql + ' , CHECK_POLICY = OFF' 
			PRINT @sql
			EXEC (@sql)

			PRINT 'login created'
		END
		ELSE
		BEGIN
			SELECT @passwordHashOld = CONVERT(varbinary(256), LOGINPROPERTY(@loginName, 'PasswordHash'))

			-- only bother updating if the password has changed since the last sync
			IF @passwordHashOld <> @passwordHashNew
			BEGIN
				EXEC master.dbo.sp_hexadecimal @passwordHashNew, @password OUTPUT

				--SET @sql = 'DROP LOGIN ' + @loginName
				--PRINT @sql
				--EXEC (@sql)

				SET @sql = 'ALTER LOGIN ' + @loginName + ' WITH PASSWORD = '
				SET @sql = @sql + CONVERT(nvarchar(512), COALESCE(@password, 'NULL'))
				SET @sql = @sql + ' HASHED, CHECK_POLICY = OFF' 
				PRINT @sql
				EXEC (@sql)

				PRINT 'login "altered"'
			END
		END

		SET @loginId = @loginId + 1
	END

	DROP TABLE #Logins

END

Estimated time of Backup / Restore [SQL Server]

Sometimes you will want to know how is the backup progress before a critical change of your environment or during the restore before setting a new high availability strategy.
This script can help you to figure out.

SELECT @@servername, command,
s.text,
start_time,
percent_complete,
CAST(((DATEDIFF(s,start_time,GetDate()))/3600) as varchar) + ' hour(s), '
+ CAST((DATEDIFF(s,start_time,GetDate())%3600)/60 as varchar) + 'min, '
+ CAST((DATEDIFF(s,start_time,GetDate())%60) as varchar) + ' sec' as running_time,
CAST((estimated_completion_time/3600000) as varchar) + ' hour(s), '
+ CAST((estimated_completion_time %3600000)/60000 as varchar) + 'min, '
+ CAST((estimated_completion_time %60000)/1000 as varchar) + ' sec' as est_time_to_go,
dateadd(second,estimated_completion_time/1000, getdate()) as est_completion_time
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) s
WHERE r.command in ('RESTORE DATABASE', 'BACKUP DATABASE', 'RESTORE LOG', 'BACKUP LOG')