SQL query to run query to all databases

Question

I am working on script in which I need to get all table record count and index created on that table for all database on server. I am able to achieve this functionality but I had to mentioned database name statically.

Here is script I am using

declare @TableList TABLE(Id int IDENTITY(1,1),DataBaseName VARCHAR(100),TableName VARCHAR(100),RecordCount INT,NameOfIndex VARCHAR(100),TypeOfIndex VARCHAR(100))
declare @TableListWithIndex TABLE(Id int IDENTITY(1,1),DataBaseName VARCHAR(100),TableName VARCHAR(100),NameOfIndex VARCHAR(100),TypeOfIndex VARCHAR(100))

INSERT INTO @TableList(DataBaseName,TableName,RecordCount)

SELECT 'DBNAME1',T.name AS [TABLE NAME], 
       I.rows AS [ROWCOUNT] 
FROM   DBNAME1.sys.tables AS T 
       INNER JOIN DBNAME1.sys.sysindexes AS I 
               ON T.object_id = I.id 
                  AND I.indid < 2 
ORDER  BY I.rows DESC

INSERT INTO @TableListWithIndex(DataBaseName,TableName,NameOfIndex,TypeOfIndex)

SELECT 
     'DBNAME1',
     TableName = t.name,
     IndexName = ind.name,
     ind.type_desc
FROM 
     DBNAME1.sys.indexes ind 
INNER JOIN 
     DBNAME1.sys.index_columns ic ON  ind.object_id = ic.object_id and ind.index_id = ic.index_id 
INNER JOIN 
     DBNAME1.sys.columns col ON ic.object_id = col.object_id and ic.column_id = col.column_id 
INNER JOIN 
     DBNAME1.sys.tables t ON ind.object_id = t.object_id 
WHERE 
     ind.is_primary_key = 0 
     AND ind.is_unique = 0 
     AND ind.is_unique_constraint = 0 
     AND t.is_ms_shipped = 0 
ORDER BY 
     t.name, ind.name, ind.index_id, ic.index_column_id 

     END  --FOR DBNAME1

update TL
     SET TL.NameOfIndex = TLW.NameOfIndex,TL.TypeOfIndex = TLW.TypeOfIndex
     from @TableList TL
     INNER JOIN @TableListWithIndex TLW ON TL.TableName = TLW.TableName 
     AND TL.DataBaseName = TLW.DataBaseName

     select * from @TableList order by Id

SO for Database2 I had to write whole code above update statement.. in order to get all records and Index name from 2 database.

But how I can achieve this functionality dynamically, where I don't need to hardcode database name.

Thanks In advance

You will have to use dynamic sql. Start with the sys.databases to get a list of all the databases. — Sean Lange
– Sean Lange, Commented Jul 23, 2015 at 19:20
Yes I know that.. But problem is with database name as variable.. — Mahajan344
– Mahajan344, Commented Jul 23, 2015 at 19:27

wiretext · Accepted Answer · 2015-07-24 04:45:58Z

1

FOR MS SQL SERVER: i have tried your one part with one table i hope you will execute in for second table also for dynamic query we have to put out statement in one variable and replace some string from that variable then execute it

declare @ds  nvarchar(22);
declare @qry  nvarchar(max);
set @ds = 'KaamKaaj';

set @qry = N' SELECT '''+ @ds + ''' as DBName , T.name AS [TABLE NAME], 
       I.rows AS [ROWCOUNT] 
FROM   @ds.sys.tables AS T 
       INNER JOIN @ds.sys.sysindexes AS I 
               ON T.object_id = I.id 
                  AND I.indid < 2 
ORDER  BY I.rows DESC';

SET @qry    =   REPLACE(@qry, '@ds', @ds)
print @qry;

declare @TableList TABLE(Id int IDENTITY(1,1),DataBaseName VARCHAR(100),TableName VARCHAR(100),RecordCount INT,NameOfIndex VARCHAR(100),TypeOfIndex VARCHAR(100))

INSERT INTO @TableList(DataBaseName,TableName,RecordCount)
exec(@qry
);

declare @qry2  nvarchar(max);

set @qry2 =  N'SELECT 
     '''+ @ds + ''' as DBName,
     t.name,
     ind.name,
     ind.type_desc
FROM 
     @ds.sys.indexes ind 
INNER JOIN 
     @ds.sys.index_columns ic ON  ind.object_id = ic.object_id and ind.index_id = ic.index_id 
INNER JOIN 
     @ds.sys.columns col ON ic.object_id = col.object_id and ic.column_id = col.column_id 
INNER JOIN 
     @ds.sys.tables t ON ind.object_id = t.object_id 
WHERE 
     ind.is_primary_key = 0 
     AND ind.is_unique = 0 
     AND ind.is_unique_constraint = 0 
     AND t.is_ms_shipped = 0 
ORDER BY 
     t.name, ind.name, ind.index_id, ic.index_column_id 

     ';
SET @qry2   =   REPLACE(@qry2, '@ds', @ds)

declare @TableListWithIndex TABLE(Id int IDENTITY(1,1),DataBaseName VARCHAR(100),TableName VARCHAR(100),NameOfIndex VARCHAR(100),TypeOfIndex VARCHAR(100))
INSERT INTO @TableListWithIndex(DataBaseName,TableName,NameOfIndex,TypeOfIndex)
exec(@qry2
);


select * from @TableList
select * from @TableListWithIndex

edited Jul 24, 2015 at 4:45

answered Jul 23, 2015 at 19:30

wiretext

3,35216 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Mahajan344 Over a year ago

Thank You so much..,I have edited rest of script.. with the help of your code.and Its running perfectly :)

wiretext Over a year ago

sorry i m late to make all SP

Sean Lange · Accepted Answer · 2015-07-23 20:06:28Z

Just for the sake of not using loops which the code previously posted would be a natural progression I wanted to put together a non-looping approach to this type of thing. Notice I had to change the datatypes of your table to be sysname because 100 characters is not long enough in some cases. I also used a temp table instead of a table variable so it will be in scope for the dynamic sql.

if OBJECT_ID('tempdb..#TableList') is not null
    drop table #TableList

create table #TableList
(
    Id int IDENTITY(1,1)
    , DataBaseName sysname
    , TableName sysname
    , RecordCount INT
    , NameOfIndex sysname
    , TypeOfIndex sysname
)

declare @Database sysname
declare @SQL nvarchar(max) = ''

select @SQL = @SQL +
    'SELECT '''
         + name + ''', '
         + 't.name collate SQL_Latin1_General_CP1_CI_AS, '
         + 'ind.name collate SQL_Latin1_General_CP1_CI_AS, '
         + 'ind.type_desc collate SQL_Latin1_General_CP1_CI_AS '
    + 'FROM [' + name + '].sys.indexes ind '
    + 'INNER JOIN [' + name + '].sys.index_columns ic ON  ind.object_id = ic.object_id and ind.index_id = ic.index_id '
    + 'INNER JOIN [' + name + '].sys.columns col ON ic.object_id = col.object_id and ic.column_id = col.column_id '
    + 'INNER JOIN [' + name + '].sys.tables t ON ind.object_id = t.object_id '
    + 'WHERE '
         + 'ind.is_primary_key = 0 '
         + 'AND ind.is_unique = 0 '
         + 'AND ind.is_unique_constraint = 0 '
         + 'AND t.is_ms_shipped = 0 '
    + 'UNION ALL '
from sys.databases           

set @SQL = 'insert #TableList(DatabaseName, TableName, NameOfIndex, TypeOfIndex) ' + left(@SQL, LEN(@SQL) - 10)

exec sp_executesql @SQL

select *
from #TableList
order by DataBaseName, TableName, NameOfIndex

Collectives™ on Stack Overflow

SQL query to run query to all databases

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related