USE [DBA]
GO

/****** Object:  StoredProcedure [dbo].[SP_SNAPSHOT_ALL_DATABASES]    Script Date: 10/28/2015 11:20:46 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

Create Proc [dbo].[SP_SNAPSHOT_ALL_DATABASES]
as 
begin
declare @sql table(stmt varchar(4000))
declare @stmt nvarchar(4000)

insert @sql (stmt)
select 'create database '+DB_NAME(database_id)+'_Snapshot on (name='+name+',filename='''+physical_name+'.ss'') as snapshot of '+DB_NAME(database_id)
from 
 sys.master_files
 where DB_NAME(database_id) not in ('tempdb','model','master')
 and type=0

declare sqlcursor cursor for
select stmt from @sql

open sqlcursor
fetch next from sqlcursor into @stmt

while @@FETCH_STATUS=0
begin

--exec sp_executesql @stmt
select @stmt
exec sp_executesql @stmt
fetch next from sqlcursor into @stmt
end
--select 'drop database  '+name from sys.databases where source_database_id is not null
end



GO