Database

openboy2010 wrote:

My Written English is not very well.

select name from master.dbo.sysdatabases

Use --Name database name one by one
select t.name, i.name
from sys.tables t, sys.indexes i
where i.object_id = t.object_id

vanikanc wrote:

I believe you cannot use the reserved word USE in stored procs?

Declare @sql varchar(max)
set @sql = 'Use AdventureWorksDW
select * from dbo.ProspectiveBuyer';
EXEC(@sql)

vanikanc wrote:

I believe you cannot use the reserved word USE in stored procs?

[ server_name . database_name . schema_name .
     | database_name .[ schema_name ] .
     | schema_name .
   ]
       table_or_view_name

DECLARE @sql VARCHAR(MAX), @db VARCHAR(255)

DECLARE c CURSOR FOR 
   SELECT [name] from master.dbo.sysdatabases 
OPEN c 
FETCH NEXT FROM c INTO @db
WHILE @@FETCH_STATUS = 0
BEGIN

SET @sql = 'Use ' + @db + SPACE(2)
SET @sql = @sql  + ' SELECT t.name As TableName, i.name As IndexName FROM sys.tables t, 
                     sys.indexes i 
					 WHERE i.object_id = t.object_id
					 ORDER BY t.Name' 

FETCH NEXT FROM c INTO @db END

CLOSE c
DEALLOCATE c

--PRINT @SQL

EXEC(@sql)

select t.name, i.name
from DatabaseName1.sys.tables t, DatabaseName1.sys.indexes i
where i.object_id = t.object_id
union
select t.name, i.name
from DatabaseName2.sys.tables t, DatabaseName2.sys.indexes i
where i.object_id = t.object_id