Mirroring Basics

http://social.technet.microsoft.com/wiki/contents/articles/28637.database-mirroring-basics.aspx

Query which searches all columns in all databases

Here "USE" is  Dynamic.

sp_msforeachdb 'Use [?];

begin

select ''?'' as DBNAME

end

SELECT t.name AS table_name,

SCHEMA_NAME(schema_id) AS schema_name,

c.name AS column_name

FROM sys.tables AS t

INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID

WHERE c.name LIKE ''%exe%''

ORDER BY schema_name, table_name;'

============================================

sp_MSforeachdb Examples 

Without using cursor we can execute a command in all the databases or on required databases by using sp_MSforeachdb.

Examples
sp_MSforeachdb 'select ''?'''   -- List all databases that are in the instance. '?' Indicated Database Name

sp_MSforeachdb 'Use [?]; exec sp_spaceused'  -- Example to execute any stored procedure

sp_MSforeachdb 'Use [?]; select * from sysfiles'  -- Example to execute any SQL Statement

sp_MSforeachdb 'Use [?]; Create table ForEachDBTest(Id Int)'  -- Creating a table in all databases

sp_MSforeachdb 'Use [?]; select * from ForEachDBTest'   -- Selecting rows from a table through all databases.

sp_MSforeachdb 'If ''?'' like ''TempDB''    -- Conditional wise execution
begin
select * from sysfiles
end '
Below command is used to display list of databases which has Create Statistics property is set to True

EXEC sp_MSforeachdb N'IF DatabasePropertyEx(''?'', ''ISAutocreateStatistics'')=1  
begin
 Print ''?''
end'

 

SQL Server Collation Change Step By Step

 Please find here:

https://gallery.technet.microsoft.com/SQL-Server-Collation-cc5cc63c

Replicated Transaction Information:Oldest distributed LSN OR The process could not execute 'sp_repldone/sp_replcounters'

Some times this is resolved by using repl_done command along with repl_flush.

EXEC sp_repldone @xactid = NULL, @xact_segno = NULL, @numtrans = 0,    @time = 0, @reset = 1

go

sp_repleflush

This time we faced issue and went unsuccessful. Then we followed below steps.

1. Stopped Log Reader Agent
2. EXEC sp_repldone @xactid = NULL, @xact_segno = NULL, @numtrans = 0,    @time = 0, @reset = 1

3. sp_repleflush
4. Started Log reader agent
5. Replication came in Sync.

Enable Change Tracking - SQL Server 2008

Setup.

1. First enable Change tracking. Here’s some sample T-SQL you can execute to start the process

ALTER DATABASE SAMPLEDB
SET CHANGE_TRACKING = ON
(CHANGE_RETENTION = 10 DAYS, AUTO_CLEANUP = ON);

GO

After it’s enabled and you want to check on which databases are affected, run this T-SQL

SELECT DB_NAME(database_id) [mydbname]
FROM SYS.CHANGE_TRACKING_DATABASES;

GO

To enable change tracking on a specific table use a query like this:

ALTER TABLE dbo.MYTABLENAME
ENABLE CHANGE_TRACKING
WITH (TRACK_COLUMNS_UPDATED = OFF);

GO

Now to check the tables that have tracking enabled, you can get that list here:

SELECT OBJECT_NAME(OBJECT_ID) [MYTABLENAME]
FROM SYS.CHANGE_TRACKING_TABLES;

GO

No changes are required to statements for insert or update statements that are enabled with change_tracking.

To find out what’s changed after enabled, you can run a query similar to the following

SELECT ct.ID, ct.SYS_CHANGE_OPERATION, c.[NAME]

FROM CHANGETABLE(CHANGES dbo.MYTABLENAME, 0) ct

JOIN dbo.MYTABLENAME c ON c.[ID] = ct.[ID];

GO