How to get the count of records in all tables of the database group by based on one single column using a single query approach.(Applicable to sql server)

 

Tried to write multiple queries into a single query with joins but none worked so ended up with below approach

SELECT 'select count(*), tenant_uuid from ' + TABLE_NAME + ' group by tenant_uuid' from (select TABLE_NAME from INFORMATION_SCHEMA.COLUMNS where COLUMN_NAME='tenant_uuid') ab

Also one analysis about row count :

Only one query which gives the correct row count of all tables  out of many available in the net in case of SQL server is below one:

SELECT Top 4 sysobjects.[name], max(sysindexes.[rows]) AS TableRows 

  FROM sysindexes INNER JOIN sysobjects ON sysindexes.[id] = sysobjects.[id] 

WHERE sysobjects.xtype = 'U' 

GROUP BY sysobjects.[name] 

ORDER BY max(rows) DESC

Tried with cursor but it is giving some other error as pasted below

declare cnt_tables cursor fast_forward for

SELECT  tab.name AS 'Table_Name',col.name as 'column_name'

FROM sys.columns col

JOIN sys.tables  tab  ON col.object_id = tab.object_id

WHERE col.name = 'tenant_uuid' 

ORDER BY Table_Name

--select table_name from information_schema.tables

open cnt_tables

declare @tablename varchar(255)

declare @columnname varchar(255)

declare @stmt nvarchar(2000)

declare @rowcount int

fetch next from cnt_tables into @tablename,@columnname

while @@fetch_status = 0

begin

select @stmt = 'select @rowcount = count(*),tenant_uuid' + 'from ' + @tablename group by + @columnname

exec sp_executesql @stmt, N'@rowcount int output', @rowcount=@rowcount OUTPUT

print N'table: ' + @tablename + ' has ' + convert(nvarchar(1000),@rowcount) + ' rows'

fetch next from cnt_tables into @tablename

end

close cnt_tables

deallocate cnt_tables

Each GROUP BY expression must contain at least one column that is not an outer reference.

How to generate alter index rebuild statement for all index based on fragmentation index in SQL server DB

 SELECT + 'ALTER INDEX ' + Indexname + '  ON ' + TABLENAME + ' REBUILD WITH (ONLINE = ON)' from  (SELECT S.name as 'Schema', T.name as 'Tablename', I.name as 'Indexname', DDIPS.avg_fragmentation_in_percent, DDIPS.page_count FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS DDIPS INNER JOIN sys.tables T on T.object_id = DDIPS.object_id INNER JOIN sys.schemas S on T.schema_id = S.schema_id INNER JOIN sys.indexes I ON I.object_id = DDIPS.object_id AND DDIPS.index_id = I.index_id WHERE DDIPS.database_id = DB_ID() and I.name is not null AND DDIPS.avg_fragmentation_in_percent > 0 ) AB

How to resolve constraint issues like ALTER TABLE statement conflicted with the FOREIGN KEY constraint

Applicable for SQL server only:

Resolve constraint violation error: 

To invalidate or disable all the constraint execute against SSMS

EXEC sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT ALL";

Check if constraint are  enabled  or not

Select 

    Schema_name(Schema_id) as SchemaName,

    object_name(Parent_object_id) as TableName,

    name as ForeignKeyConstraintName,

    Case When Is_disabled=1 Then 'No'

    ELSE 'Yes' End as IsEnabled

    from sys.foreign_keys

Validate the constraint:

EXEC sp_msforeachtable "ALTER TABLE ? WITH CHECK CHECK CONSTRAINT ALL";

It will fail

The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "FK_PAR_CRON_TRIGGERS_CHL_CRON_TRIGGERS".

The conflict occurred in database "demo06947", table "dbo.CHL_CRON_TRIGGERS".

delete the record: 

select SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP from dbo.CHL_CRON_TRIGGERS a

where TRIGGER_NAME NOT IN(select TRIGGER_NAME from dbo.PAR_CRON_TRIGGERS)

Create backup table before deletion

select SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP INTO [demo06947].dbo.temp_constraint_CRON_TRIGGERS

from dbo.CHL_CRON_TRIGGERS a where TRIGGER_NAME NOT IN(select TRIGGER_NAME from dbo.PAR_CRON_TRIGGERS)

Some SQL server useful views:

select containment,containment_desc,is_in_standby,state_desc from sys.databases