2 deleted 5 characters in body
source | link
USE [master];
GO

DECLARE @DatabaseName VARCHAR(32)   
DECLARE @SQL NVARCHAR(max)


--get engine version
DECLARE @MajorVersion TINYINT
SET @MajorVersion = LEFT(CAST(SERVERPROPERTY('ProductVersion') AS nvarchar(4000)),CHARINDEX('.',CAST(SERVERPROPERTY('ProductVersion') AS nvarchar(4000)))-1);

IF OBJECT_ID('tempdb..#databases') IS NOT NULL
BEGIN
DROP TABLE #databases;
END
CREATE TABLE #databases (dbname sysname);

--if pre 2012 meaning no AG
--grab all online databases
IF @MajorVersion < 11 -- pre-SQL2012
BEGIN

       INSERT INTO #databases
       --only choose online databases and exclude single user mode
       SELECT name FROM sys.databases 
       WHERE [STATE] = 0
       AND   [user_access]>0[user_access]=0
       AND   [NAME] NOT IN ('master','model','msdb','tempdb') ; 
END
ELSE --post SQL2012
BEGIN
       INSERT INTO #databases
       ----only choose online databases and exclude single user mode
       SELECT name FROM sys.databases 
       WHERE [STATE] = 0
       AND   [user_access]>0[user_access]=0
       AND   [NAME] NOT IN ('master','model','msdb','tempdb');  
END
--get secondary replica db list
IF OBJECT_ID('tempdb..#AGdatabases') IS NOT NULL
BEGIN
DROP TABLE #AGdatabases;
END

CREATE TABLE #AGdatabases
(dbname SYSNAME);

IF EXISTS (SELECT
AG.name AS [Name],
ISNULL(agstates.primary_replica, '') AS [PrimaryReplicaServerName],
ISNULL(arstates.role, 3) AS [LocalReplicaRole]
FROM master.sys.availability_groups AS AG
LEFT OUTER JOIN master.sys.dm_hadr_availability_group_states as agstates
    ON AG.group_id = agstates.group_id
INNER JOIN master.sys.availability_replicas AS AR
    ON AG.group_id = AR.group_id
INNER JOIN master.sys.dm_hadr_availability_replica_states AS arstates
    ON AR.replica_id = arstates.replica_id AND arstates.is_local = 1
)

BEGIN
INSERT INTO #AGdatabases
SELECT DISTINCT
dbcs.database_name AS [DatabaseName]
FROM master.sys.availability_groups AS AG
LEFT OUTER JOIN master.sys.dm_hadr_availability_group_states as agstates
   ON AG.group_id = agstates.group_id
INNER JOIN master.sys.availability_replicas AS AR
   ON AG.group_id = AR.group_id
INNER JOIN master.sys.dm_hadr_availability_replica_states AS arstates
   ON AR.replica_id = arstates.replica_id AND arstates.is_local = 1
INNER JOIN master.sys.dm_hadr_database_replica_cluster_states AS dbcs
   ON arstates.replica_id = dbcs.replica_id
LEFT OUTER JOIN master.sys.dm_hadr_database_replica_states AS dbrs
   ON dbcs.replica_id = dbrs.replica_id AND dbcs.group_database_id = dbrs.group_database_id
WHERE ISNULL(arstates.role, 3) = 2 AND ISNULL(dbcs.is_database_joined, 0) = 1
ORDER BY  dbcs.database_name;
END
--exclude secondary replica databases
DELETE FROM #databases WHERE dbname in
(SELECT dbname FROM #AGdatabases);
--END


DECLARE Deploy_Code CURSOR LOCAL FOR
SELECT dbname FROM #databases;

OPEN Deploy_Code  
FETCH NEXT FROM Deploy_Code INTO @DatabaseName  
WHILE @@FETCH_STATUS = 0  
BEGIN  

       SELECT @SQL = 'USE '+ '[' + @DatabaseName + ']' +'; '+ 'Put your code here'+ '; '

       --EXEC sp_executesql @SQL
       PRINT @SQL

FETCH NEXT FROM Deploy_Code INTO @DatabaseName  
END  
CLOSE Deploy_Code  
DEALLOCATE Deploy_Code
USE [master];
GO

DECLARE @DatabaseName VARCHAR(32)   
DECLARE @SQL NVARCHAR(max)


--get engine version
DECLARE @MajorVersion TINYINT
SET @MajorVersion = LEFT(CAST(SERVERPROPERTY('ProductVersion') AS nvarchar(4000)),CHARINDEX('.',CAST(SERVERPROPERTY('ProductVersion') AS nvarchar(4000)))-1);

IF OBJECT_ID('tempdb..#databases') IS NOT NULL
BEGIN
DROP TABLE #databases;
END
CREATE TABLE #databases (dbname sysname);

--if pre 2012 meaning no AG
--grab all online databases
IF @MajorVersion < 11 -- pre-SQL2012
BEGIN

       INSERT INTO #databases
       --only choose online databases and exclude single user mode
       SELECT name FROM sys.databases 
       WHERE [STATE] = 0
       AND   [user_access]>0
       AND   [NAME] NOT IN ('master','model','msdb','tempdb') ; 
END
ELSE --post SQL2012
BEGIN
       INSERT INTO #databases
       ----only choose online databases and exclude single user mode
       SELECT name FROM sys.databases 
       WHERE [STATE] = 0
       AND   [user_access]>0
       AND   [NAME] NOT IN ('master','model','msdb','tempdb');  
END
--get secondary replica db list
IF OBJECT_ID('tempdb..#AGdatabases') IS NOT NULL
BEGIN
DROP TABLE #AGdatabases;
END

CREATE TABLE #AGdatabases
(dbname SYSNAME);

IF EXISTS (SELECT
AG.name AS [Name],
ISNULL(agstates.primary_replica, '') AS [PrimaryReplicaServerName],
ISNULL(arstates.role, 3) AS [LocalReplicaRole]
FROM master.sys.availability_groups AS AG
LEFT OUTER JOIN master.sys.dm_hadr_availability_group_states as agstates
    ON AG.group_id = agstates.group_id
INNER JOIN master.sys.availability_replicas AS AR
    ON AG.group_id = AR.group_id
INNER JOIN master.sys.dm_hadr_availability_replica_states AS arstates
    ON AR.replica_id = arstates.replica_id AND arstates.is_local = 1
)

BEGIN
INSERT INTO #AGdatabases
SELECT DISTINCT
dbcs.database_name AS [DatabaseName]
FROM master.sys.availability_groups AS AG
LEFT OUTER JOIN master.sys.dm_hadr_availability_group_states as agstates
   ON AG.group_id = agstates.group_id
INNER JOIN master.sys.availability_replicas AS AR
   ON AG.group_id = AR.group_id
INNER JOIN master.sys.dm_hadr_availability_replica_states AS arstates
   ON AR.replica_id = arstates.replica_id AND arstates.is_local = 1
INNER JOIN master.sys.dm_hadr_database_replica_cluster_states AS dbcs
   ON arstates.replica_id = dbcs.replica_id
LEFT OUTER JOIN master.sys.dm_hadr_database_replica_states AS dbrs
   ON dbcs.replica_id = dbrs.replica_id AND dbcs.group_database_id = dbrs.group_database_id
WHERE ISNULL(arstates.role, 3) = 2 AND ISNULL(dbcs.is_database_joined, 0) = 1
ORDER BY  dbcs.database_name;
END
--exclude secondary replica databases
DELETE FROM #databases WHERE dbname in
(SELECT dbname FROM #AGdatabases);
--END


DECLARE Deploy_Code CURSOR LOCAL FOR
SELECT dbname FROM #databases;

OPEN Deploy_Code  
FETCH NEXT FROM Deploy_Code INTO @DatabaseName  
WHILE @@FETCH_STATUS = 0  
BEGIN  

       SELECT @SQL = 'USE '+ '[' + @DatabaseName + ']' +'; '+ 'Put your code here'+ '; '

       --EXEC sp_executesql @SQL
       PRINT @SQL

FETCH NEXT FROM Deploy_Code INTO @DatabaseName  
END  
CLOSE Deploy_Code  
DEALLOCATE Deploy_Code
USE [master];
GO

DECLARE @DatabaseName VARCHAR(32)   
DECLARE @SQL NVARCHAR(max)


--get engine version
DECLARE @MajorVersion TINYINT
SET @MajorVersion = LEFT(CAST(SERVERPROPERTY('ProductVersion') AS nvarchar(4000)),CHARINDEX('.',CAST(SERVERPROPERTY('ProductVersion') AS nvarchar(4000)))-1);

IF OBJECT_ID('tempdb..#databases') IS NOT NULL
BEGIN
DROP TABLE #databases;
END
CREATE TABLE #databases (dbname sysname);

--if pre 2012 meaning no AG
--grab all online databases
IF @MajorVersion < 11 -- pre-SQL2012
BEGIN

       INSERT INTO #databases
       --only choose online databases and exclude single user mode
       SELECT name FROM sys.databases 
       WHERE [STATE] = 0
       AND   [user_access]=0
       AND   [NAME] NOT IN ('master','model','msdb','tempdb') ; 
END
ELSE --post SQL2012
BEGIN
       INSERT INTO #databases
       ----only choose online databases and exclude single user mode
       SELECT name FROM sys.databases 
       WHERE [STATE] = 0
       AND   [user_access]=0
       AND   [NAME] NOT IN ('master','model','msdb','tempdb');  

--get secondary replica db list
IF OBJECT_ID('tempdb..#AGdatabases') IS NOT NULL
BEGIN
DROP TABLE #AGdatabases;
END

CREATE TABLE #AGdatabases
(dbname SYSNAME);

IF EXISTS (SELECT
AG.name AS [Name],
ISNULL(agstates.primary_replica, '') AS [PrimaryReplicaServerName],
ISNULL(arstates.role, 3) AS [LocalReplicaRole]
FROM master.sys.availability_groups AS AG
LEFT OUTER JOIN master.sys.dm_hadr_availability_group_states as agstates
    ON AG.group_id = agstates.group_id
INNER JOIN master.sys.availability_replicas AS AR
    ON AG.group_id = AR.group_id
INNER JOIN master.sys.dm_hadr_availability_replica_states AS arstates
    ON AR.replica_id = arstates.replica_id AND arstates.is_local = 1
)

BEGIN
INSERT INTO #AGdatabases
SELECT DISTINCT
dbcs.database_name AS [DatabaseName]
FROM master.sys.availability_groups AS AG
LEFT OUTER JOIN master.sys.dm_hadr_availability_group_states as agstates
   ON AG.group_id = agstates.group_id
INNER JOIN master.sys.availability_replicas AS AR
   ON AG.group_id = AR.group_id
INNER JOIN master.sys.dm_hadr_availability_replica_states AS arstates
   ON AR.replica_id = arstates.replica_id AND arstates.is_local = 1
INNER JOIN master.sys.dm_hadr_database_replica_cluster_states AS dbcs
   ON arstates.replica_id = dbcs.replica_id
LEFT OUTER JOIN master.sys.dm_hadr_database_replica_states AS dbrs
   ON dbcs.replica_id = dbrs.replica_id AND dbcs.group_database_id = dbrs.group_database_id
WHERE ISNULL(arstates.role, 3) = 2 AND ISNULL(dbcs.is_database_joined, 0) = 1
ORDER BY  dbcs.database_name;
END
--exclude secondary replica databases
DELETE FROM #databases WHERE dbname in
(SELECT dbname FROM #AGdatabases);
END


DECLARE Deploy_Code CURSOR LOCAL FOR
SELECT dbname FROM #databases;

OPEN Deploy_Code  
FETCH NEXT FROM Deploy_Code INTO @DatabaseName  
WHILE @@FETCH_STATUS = 0  
BEGIN  

       SELECT @SQL = 'USE '+ '[' + @DatabaseName + ']' +'; '+ 'Put your code here'+ '; '

       --EXEC sp_executesql @SQL
       PRINT @SQL

FETCH NEXT FROM Deploy_Code INTO @DatabaseName  
END  
CLOSE Deploy_Code  
DEALLOCATE Deploy_Code
1
source | link

If you prefer to use tsql and exclude system, offline, single user mode and Availability group read_only databases following script will do that. You will need to put your code in the section I marked with 'Put your code here'.

USE [master];
GO

DECLARE @DatabaseName VARCHAR(32)   
DECLARE @SQL NVARCHAR(max)


--get engine version
DECLARE @MajorVersion TINYINT
SET @MajorVersion = LEFT(CAST(SERVERPROPERTY('ProductVersion') AS nvarchar(4000)),CHARINDEX('.',CAST(SERVERPROPERTY('ProductVersion') AS nvarchar(4000)))-1);

IF OBJECT_ID('tempdb..#databases') IS NOT NULL
BEGIN
DROP TABLE #databases;
END
CREATE TABLE #databases (dbname sysname);

--if pre 2012 meaning no AG
--grab all online databases
IF @MajorVersion < 11 -- pre-SQL2012
BEGIN

       INSERT INTO #databases
       --only choose online databases and exclude single user mode
       SELECT name FROM sys.databases 
       WHERE [STATE] = 0
       AND   [user_access]>0
       AND   [NAME] NOT IN ('master','model','msdb','tempdb') ; 
END
ELSE --post SQL2012
BEGIN
       INSERT INTO #databases
       ----only choose online databases and exclude single user mode
       SELECT name FROM sys.databases 
       WHERE [STATE] = 0
       AND   [user_access]>0
       AND   [NAME] NOT IN ('master','model','msdb','tempdb');  
END
--get secondary replica db list
IF OBJECT_ID('tempdb..#AGdatabases') IS NOT NULL
BEGIN
DROP TABLE #AGdatabases;
END

CREATE TABLE #AGdatabases
(dbname SYSNAME);

IF EXISTS (SELECT
AG.name AS [Name],
ISNULL(agstates.primary_replica, '') AS [PrimaryReplicaServerName],
ISNULL(arstates.role, 3) AS [LocalReplicaRole]
FROM master.sys.availability_groups AS AG
LEFT OUTER JOIN master.sys.dm_hadr_availability_group_states as agstates
    ON AG.group_id = agstates.group_id
INNER JOIN master.sys.availability_replicas AS AR
    ON AG.group_id = AR.group_id
INNER JOIN master.sys.dm_hadr_availability_replica_states AS arstates
    ON AR.replica_id = arstates.replica_id AND arstates.is_local = 1
)

BEGIN
INSERT INTO #AGdatabases
SELECT DISTINCT
dbcs.database_name AS [DatabaseName]
FROM master.sys.availability_groups AS AG
LEFT OUTER JOIN master.sys.dm_hadr_availability_group_states as agstates
   ON AG.group_id = agstates.group_id
INNER JOIN master.sys.availability_replicas AS AR
   ON AG.group_id = AR.group_id
INNER JOIN master.sys.dm_hadr_availability_replica_states AS arstates
   ON AR.replica_id = arstates.replica_id AND arstates.is_local = 1
INNER JOIN master.sys.dm_hadr_database_replica_cluster_states AS dbcs
   ON arstates.replica_id = dbcs.replica_id
LEFT OUTER JOIN master.sys.dm_hadr_database_replica_states AS dbrs
   ON dbcs.replica_id = dbrs.replica_id AND dbcs.group_database_id = dbrs.group_database_id
WHERE ISNULL(arstates.role, 3) = 2 AND ISNULL(dbcs.is_database_joined, 0) = 1
ORDER BY  dbcs.database_name;
END
--exclude secondary replica databases
DELETE FROM #databases WHERE dbname in
(SELECT dbname FROM #AGdatabases);
--END


DECLARE Deploy_Code CURSOR LOCAL FOR
SELECT dbname FROM #databases;

OPEN Deploy_Code  
FETCH NEXT FROM Deploy_Code INTO @DatabaseName  
WHILE @@FETCH_STATUS = 0  
BEGIN  

       SELECT @SQL = 'USE '+ '[' + @DatabaseName + ']' +'; '+ 'Put your code here'+ '; '

       --EXEC sp_executesql @SQL
       PRINT @SQL

FETCH NEXT FROM Deploy_Code INTO @DatabaseName  
END  
CLOSE Deploy_Code  
DEALLOCATE Deploy_Code