Prev: sp_who2 and system background processes
Next: Question on SET COMPATIBILITY_LEVEL
From: Uri Dimant on 11 Apr 2010 02:36
Hi
Well, you can open a cursorn and run thru all databases to build dynamic sql

DECLARE @dbsizeStatement nvarchar(4000),@sql NVARCHAR(MAX)

DECLARE dbsize CURSOR LOCAL FAST_FORWARD

FOR

SELECT name FROM sys.databases ORDER by name

OPEN dbsize

WHILE 1 = 1

BEGIN

FETCH NEXT FROM dbsize INTO @dbsizeStatement

IF @@FETCH_STATUS <> 0 BREAK

SELECT @sql='SELECT * FROM ['+@dbsizeStatement+'].sys.TABLES'

EXEC sp_executesql @sql, N'@dbsizeStatement nvarchar(4000)',@dbsizeStatement

SET @sql=''

END

CLOSE dbsize

DEALLOCATE dbsize



"Pingx" <Pingx(a)discussions.microsoft.com> wrote in message
news:98BD71B6-A644-4509-8964-1C4B5D86205D(a)microsoft.com...
> sys.tables has the same data as the other two cases. you have to know the
> db
> name first.
>
> Pingx
>
> "Uri Dimant" wrote:
>
>> Hi
>> SELECT * FROM sys.tables
>>
>> "Pingx" <Pingx(a)discussions.microsoft.com> wrote in message
>> news:0FC72149-89F1-4922-8B5B-534DBCF24919(a)microsoft.com...
>> > Dear SQL Server Export,
>> > How do I find a certain table in sql server assuming I do not know the
>> > name
>> > of the database. sysobjects does not work. and
>> > INFORMATION_SCHEMA.TABLES
>> > does
>> > not work either. You have to know the db name in both cases.
>> >
>> >
>>
>>
>> .
>>


First  |  Prev  | 
Pages: 1 2
Prev: sp_who2 and system background processes
Next: Question on SET COMPATIBILITY_LEVEL