October 23, 2017 at 1:09 am
I have to design a script to find all tables in a database with below criteria.
1. Identify all tables should have more than one million records and doesn't have any columnstoreindex on it.
2. Identify all the tables which contain both columnsstoreindex and non-clustered index.
October 23, 2017 at 2:36 am
Sounds a little bit like a homework question. Be that as it may, you need to join the sys.tables, sys.indexes and sys.partitions catalog views to solve this problem. Have a go, and post back if you struggle with something in particular.
John
October 23, 2017 at 3:11 am
I am new to SQL, So can you help me .
October 23, 2017 at 3:20 am
Yes, of course. Show me what you've tried, and I'll give you some pointers.
John
October 23, 2017 at 3:25 am
DECLARE @TableRowCounts TABLE ([TableName] VARCHAR(128), [RowCount] INT) ;
INSERT INTO @TableRowCounts ([TableName], [RowCount])
EXEC sp_MSforeachtable 'SELECT ''?'' [TableName],COUNT(*) [RowCount] FROM ?' ;
SELECT [TableName], [RowCount]
FROM @TableRowCounts trc
INNER JOIN sys.indexes AS i ON i.OBJECT_ID = OBJECT_ID(trc.TableName)
WHERE type_desc <> 'CLUSTERED COLUMNSTORE'
GROUP BY [TableName], [RowCount]
HAVING [RowCount] > 1000000
ORDER BY [TableName]
GO
But, this is not giving correct results for 1st requirement . Also can you help me on requirement 2?
October 23, 2017 at 3:35 am
Don't bother with sp_MSforeachtable. Join to sys.partitions instead - that has a column with the row count in it. Start by returning a result set with a row count for all tables. You only need sys.tables and sys.partitions for that. Then we can look at filtering down your results to find the tables with or without the types of indexes you're interested in.
John
October 23, 2017 at 3:37 am
I am not getting. Please help
October 23, 2017 at 3:49 am
Here's how to get a row count for each table:SELECT
object_id
, SUM(rows) AS NoofRows
FROM sys.partitions
WHERE index_id IN (0,1) -- clustered index or heap
GROUP BY object_id
That can be simplified to this if you have no partitioned tables in your database:SELECT
object_id
, rows AS NoofRows
FROM sys.partitions
WHERE index_id IN (0,1) -- clustered index or heap
Now just join that to sys.tables to get the table names with row counts. Once we've got that, we can look at the indexes, but let's do one step at a time.
October 23, 2017 at 3:50 am
ramrajan - Monday, October 23, 2017 3:37 AMI am not getting. Please help
Have a look at the tables that John has said to start with. This query should get you started on what he's talking about:SELECT *
FROM sys.tables t
LEFT JOIN sys.indexes i ON t.object_id = i.object_id;
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
October 23, 2017 at 3:53 am
Can you please share the complete query instead of just half ?
October 23, 2017 at 3:54 am
ramrajan - Monday, October 23, 2017 3:53 AMCan you please share the complete query instead of just half ?
No. We're not here to do your job (or homework) for you. We're happy to help, but if we just give you the answer, you won't learn anything.
John
October 23, 2017 at 4:04 am
Please i am really a new bee.
October 23, 2017 at 4:16 am
Please show some mercy on me, I have to complete this, I have only 30mins from now to share this report.
October 23, 2017 at 5:10 am
ramrajan - Monday, October 23, 2017 4:16 AMPlease show some mercy on me, I have to complete this, I have only 30mins from now to share this report.
Why are you in a position where you have an hour or 2 to supply a report on things you don't know?
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
October 23, 2017 at 5:14 am
I am basically a Teradata developer with no knowledge of SQL but this report is really critical to other teams.
Viewing 15 posts - 1 through 15 (of 33 total)
You must be logged in to reply to this topic. Login to reply