Query at database level
-
Hello, I would like to write a select statement at the database server level, like trying to find all the existing indexes for all the databases in that server - something like this? But for all the databases in that given server and not individual database servers. This below piece works only for each databases. -- select t.name, i.name from sys.tables t, sys.indexes i where i.object_id = t.object_id -- thanks!
-
Hello, I would like to write a select statement at the database server level, like trying to find all the existing indexes for all the databases in that server - something like this? But for all the databases in that given server and not individual database servers. This below piece works only for each databases. -- select t.name, i.name from sys.tables t, sys.indexes i where i.object_id = t.object_id -- thanks!
AFAIK It's not implicitly possible with T-SQL as Particular index information is not stored globally, It's in corresponding DB. But there's some suggestion that you may apply to get. a) Get name of all the database in Server
select name from master.dbo.sysdatabases
b) Take database one by one from the above query result and make a custom query and execute it using
EXECstatement. LikeUse --Name database name one by one
select t.name, i.name
from sys.tables t, sys.indexes i
where i.object_id = t.object_idc) Store each result in one temperory table with the required column. d) Finally SELECT * FROM #Temptable I Hope you are getting me.
Regards, Hiren.
My Recent Article: - Way to know which control have raised a postback
My Recent Tip/Trick: - The ?? Operator. -
AFAIK It's not implicitly possible with T-SQL as Particular index information is not stored globally, It's in corresponding DB. But there's some suggestion that you may apply to get. a) Get name of all the database in Server
select name from master.dbo.sysdatabases
b) Take database one by one from the above query result and make a custom query and execute it using
EXECstatement. LikeUse --Name database name one by one
select t.name, i.name
from sys.tables t, sys.indexes i
where i.object_id = t.object_idc) Store each result in one temperory table with the required column. d) Finally SELECT * FROM #Temptable I Hope you are getting me.
Regards, Hiren.
My Recent Article: - Way to know which control have raised a postback
My Recent Tip/Trick: - The ?? Operator.Thanks for the reply, Hiren. I have tried to use the cursor to loop through all the databases, passing in one database at a time to th inner cursor at the master level database. But it is not getting the correct results. I believe you cannot use the reserved word USE in stored procs?
-
Thanks for the reply, Hiren. I have tried to use the cursor to loop through all the databases, passing in one database at a time to th inner cursor at the master level database. But it is not getting the correct results. I believe you cannot use the reserved word USE in stored procs?
vanikanc wrote:
I believe you cannot use the reserved word USE in stored procs?
I know that we can't use it plainly. IMO like following way.
Declare @sql varchar(max)
set @sql = 'Use AdventureWorksDW
select * from dbo.ProspectiveBuyer';
EXEC(@sql)Regards, Hiren.
My Recent Article: - Way to know which control have raised a postback
My Recent Tip/Trick: - The ?? Operator. -
Hello, I would like to write a select statement at the database server level, like trying to find all the existing indexes for all the databases in that server - something like this? But for all the databases in that given server and not individual database servers. This below piece works only for each databases. -- select t.name, i.name from sys.tables t, sys.indexes i where i.object_id = t.object_id -- thanks!
I don't know a way to do what you require using SQL/T-SQL but it can definitely be done using SMO (Example of a C# utility using SMO to script indexes[^]) or a combination of SMO and PowerShell.
Henry Minute Do not read medical books! You could die of a misprint. - Mark Twain Girl: (staring) "Why do you need an icy cucumber?" “I want to report a fraud. The government is lying to us all.”
-
Thanks for the reply, Hiren. I have tried to use the cursor to loop through all the databases, passing in one database at a time to th inner cursor at the master level database. But it is not getting the correct results. I believe you cannot use the reserved word USE in stored procs?
vanikanc wrote:
I believe you cannot use the reserved word USE in stored procs?
A table name can be specified with the following form.
[ server_name . database_name . schema_name .
| database_name .[ schema_name ] .
| schema_name .
]
table_or_view_nameThat allows one to do a select from another database. Thus for example the 'master.dbo.sysdatabases' is using the form that starts with the database name.
-
Hello, I would like to write a select statement at the database server level, like trying to find all the existing indexes for all the databases in that server - something like this? But for all the databases in that given server and not individual database servers. This below piece works only for each databases. -- select t.name, i.name from sys.tables t, sys.indexes i where i.object_id = t.object_id -- thanks!
Try Like this in Your SP If You Know all the Database Names.
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_idRegards Senthil Raja.J
modified on Thursday, December 30, 2010 4:34 AM
-
Try Like this in Your SP If You Know all the Database Names.
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_idRegards Senthil Raja.J
modified on Thursday, December 30, 2010 4:34 AM
Please wrap the code in PRE tag.
Regards, Hiren.
My Recent Article: - Way to know which control have raised a postback
My Recent Tip/Trick: - The ?? Operator. -
Thanks for the reply, Hiren. I have tried to use the cursor to loop through all the databases, passing in one database at a time to th inner cursor at the master level database. But it is not getting the correct results. I believe you cannot use the reserved word USE in stored procs?
Hi vanikanc, I got the solution regarding BUT I am going to publish a TIP/TRICK on that today so please wait and I will provide you a link to TIP/TRICK Soon.
Regards, Hiren.
My Recent Article: - Way to know which control have raised a postback
My Recent Tip/Trick: - The ?? Operator. -
Thanks for the reply, Hiren. I have tried to use the cursor to loop through all the databases, passing in one database at a time to th inner cursor at the master level database. But it is not getting the correct results. I believe you cannot use the reserved word USE in stored procs?
Hi, try something like this and build on a sql string, I hope this helps.
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
BEGINSET @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)
TJR We Came! We Saw! We Listened! We Eliminated Ambiguity and developed a system the user wanted, not what we thought they wanted. Enough Said!