It’s possible that you’d want to restore all the databases you have backed up to  a specific folder. This will generate the scripts to restore those and move the files to a new destination. Copy the output from this query into a new query window and modify as needed before running.

Part of this code was taken from StackOverflow – I think this post in particular, but it’s hard to say for sure.

SET NOCOUNT ON
declare @folder as varchar(1000) = 'C:\Backups\ '
declare @moveToDrive as varchar(20) = 'M'
declare @allFiles as table(id int identity(1,1),file_nm varchar(200), depth_no int, is_file bit, fullPath varchar(1200)
,restoreFilenames varchar(2000))
DECLARE @fileListTable TABLE (
[LogicalName] NVARCHAR(128),
[PhysicalName] NVARCHAR(260),
[Type] CHAR(1),
[FileGroupName] NVARCHAR(128),
[Size] NUMERIC(20,0),
[MaxSize] NUMERIC(20,0),
[FileID] BIGINT,
[CreateLSN] NUMERIC(25,0),
[DropLSN] NUMERIC(25,0),
[UniqueID] UNIQUEIDENTIFIER,
[ReadOnlyLSN] NUMERIC(25,0),
[ReadWriteLSN] NUMERIC(25,0),
[BackupSizeInBytes] BIGINT,
[SourceBlockSize] INT,
[FileGroupID] INT,
[LogGroupGUID] UNIQUEIDENTIFIER,
[DifferentialBaseLSN] NUMERIC(25,0),
[DifferentialBaseGUID] UNIQUEIDENTIFIER,
[IsReadOnly] BIT,
[IsPresent] BIT,
[TDEThumbprint] VARBINARY(32) -- remove this column if using SQL 2005
,allFilesId int
)

insert into @allFiles (file_nm, depth_no, is_file)
EXEC Master.dbo.xp_DirTree @folder,1,1

update @allFiles set fullPath = @folder + file_nm
update @allFiles set restoreFilenames = 'RESTORE FILELISTONLY FROM DISK='''+fullPath+''''

--select id, file_nm, fullPath,restoreFilenames
--from @allFiles

DECLARE @curFile_id as int, @restoreScript as varchar(2000)
set @curFile_id = 1
while @curFile_id <= (select MAX(id) from @allFiles)
begin
select @restoreScript=restoreFilenames from @allFiles where id=@curFile_id
insert into @fileListTable ([LogicalName],[PhysicalName],[Type],[FileGroupName],[Size],[MaxSize],[FileID],[CreateLSN],[DropLSN],[UniqueID],[ReadOnlyLSN],[ReadWriteLSN],[BackupSizeInBytes],[SourceBlockSize],[FileGroupID],[LogGroupGUID],[DifferentialBaseLSN],[DifferentialBaseGUID],[IsReadOnly],[IsPresent],[TDEThumbprint])
exec(@restoreScript)
update @fileListTable set allFilesId=@curFile_id where allFilesId is null
set @curFile_id += 1
END

select allFilesId, LogicalName, @moveToDrive+SUBSTRING(PhysicalName,2,1000),
',MOVE '''+LogicalName+''' TO '''@moveToDrive+SUBSTRING(PhysicalName,2,1000)+''''
from @fileListTable
union
select id, '!'+SUBSTRING(file_nm, 1, PATINDEX('%_FULL%',file_nm)-1),'',
'RESTORE DATABASE ['+SUBSTRING(file_nm, 1, PATINDEX('%_FULL%',file_nm)-1)+'] FROM DISK='''+fullPath+''' WITH STATS=3,NORECOVERY'
from @allFiles
ORDER BY allFilesId, LogicalName




SET NOCOUNT OFF

Sometimes it’s necessary or nice to export all the databases and tables on a server. This simple script will give you information about every table you have access. As a DBA, you can uncomment the “execute as…” and “revert” lines to determine what tables another user can see.

-- execute as login = 'newUser'
set nocount on

Declare @Output as table (DBName varchar(200), TableName varchar(200), TAB_VIEW varchar(10), ColName varchar(200), ColType varchar(200), PK bit)
declare @DBName as VARCHAR(100) = 'master'
declare @SQL as NVARCHAR(max)
declare @CurDB as int = 5

 

while @CurDB <= (select MAX(database_id) from master.sys.databases)
begin
SELECT @DBName = name from master.sys.databases where database_id = @CurDB

begin try
print 'Checking ' + @DBName + '…'
SET @SQL = 'select "' + @DBName + "' as DBName, tables.name as TableName, tables.TAB_VIEW, columns.name as ColName,
case types.name
when "varchar" then "varchar(" + ISNULL(NULLIF(NULLIF(convert(varchar(4),columns.max_length) ,-1),0),"MAX") + ")"
when "char" then "char(" + ISNULL(NULLIF(NULLIF(convert(varchar(4),columns.max_length) ,-1),0),"MAX") + ")"
when "nvarchar" then "nvarchar(" + ISNULL(NULLIF(NULLIF(convert(varchar(4),columns.max_length/2),-1),0),"MAX") + ")"
when "nchar" then "nchar(" + ISNULL(NULLIF(NULLIF(convert(varchar(4),columns.max_length/2),-1),0),"MAX") + ")"
when "binary" then "binary(" + ISNULL(NULLIF(NULLIF(convert(varchar(4),columns.max_length) ,-1),0),"MAX") + ")"
when "varbinary" then "varbinary(" + ISNULL(NULLIF(NULLIF(convert(varchar(4),columns.max_length) ,-1),0),"MAX") + ")"

when "numeric" then "numeric(" + convert(varchar(4),columns.precision) + "," + convert(varchar(4),columns.scale) + ")"
when "decimal" then "decimal(" + convert(varchar(4),columns.precision) + "," + convert(varchar(4),columns.scale) + ")"

when "float" then "float" + ISNULL(NULLIF("("+convert(varchar(4),columns.precision)+")","(53)"),"")
–when "varbinary" then "varbinary(" + ISNULL(NULLIF(NULLIF(convert(varchar(4),columns.max_length) ,-1),0),"MAX") + ")"
else types.name end as ColType–, columns.*
,isnull(PK.[PrimaryKey],0) as [PrimaryKey]

from [' + @DBName + '].sys.columns
inner join (SELECT NAME, object_id, is_ms_shipped, "TABLE" AS TAB_VIEW FROM [' + @DBName + '].sys.tables
union SELECT NAME, object_id, is_ms_shipped, "VIEW" AS TAB_VIEW FROM [' + @DBName + '].sys.views ) as tables
on tables.object_id = columns.object_id
left join [' + @DBName + '].sys.types
on types.user_type_id = columns.user_type_id
outer apply (
select case when si.object_id is not null then 1 else 0 end as [PrimaryKey]
from [' + @DBName + '].sys.indexes si
inner join [' + @DBName + '].sys.index_columns ic
ON si.OBJECT_ID = ic.OBJECT_ID
AND si.index_id = ic.index_id
and si.is_primary_key = 1
where si.object_id = tables.object_id
and ic.column_id = columns.column_id
) as PK

where tables.is_ms_shipped = 0'

insert @Output
exec sp_sqlexec @SQL
print '... Success'
end try
begin catch
print '... Error: ' + error_message()
end catch

set @CurDB += 1
end

select * from @Output

print 'COMPLETE!'
-- revert