Tuesday, November 29, 2022

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.



Monday, November 21, 2022

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

Thursday, July 21, 2022

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



Thursday, June 10, 2021

How to filter data based on difference of two date Column when date or timestamp values are given in Traditional Chinese format.(in Oracle)

 Some of the Chinese Date format that should be inside the column where we do the filtering.


select sysdate from dual


10-6月 -2021 is 10th of June 2021


22-10月 -20


24-9月 -20 02.42.58.000000000 上午


453642 07-12月-20 06.54.59.000000000 上午 07-12月-20 06.58.00.000000000 上午


We can filter based on the below SQL:


---One way is to convert the Traditional Chinese date to English calender date and compare.


select * from USER_DET_TABLES where  Start_Datetime between to_date('22-OCT-20','DD-MON-YY', 'NLS_DATE_LANGUAGE = ''American'' ') and to_date('25-FEB-21','DD-MON-YY', 'NLS_DATE_LANGUAGE = ''American'' ')



--Other way is to directly pass the Traditional Chinese date in To_Date() and use NLS_DATE_LANGUAGE


select * from jobstatus_users_tab where  Start_Datetime between to_date('22-10月 -20','DD-MON-YY', 'NLS_DATE_LANGUAGE = ''Traditional Chinese'' ') and to_date('24-2月 -21','DD-MON-YY', 'NLS_DATE_LANGUAGE = ''Traditional Chinese'' ')


==


Specifying NLS Parameters in SQL Functions and Valid Use case of NLS parameters in SQL functions in Oracle


NLS parameters are specified in SQL functions as 'parameter = value'. For example:


'NLS_DATE_LANGUAGE = AMERICAN'


The following NLS parameters can be specified in SQL functions:



NLS_DATE_LANGUAGE

NLS_NUMERIC_CHARACTERS

NLS_CURRENCY

NLS_ISO_CURRENCY

NLS_DUAL_CURRENCY

NLS_CALENDAR

NLS_SORT

Table: SQL Functions and Their Valid NLS Parameters shows which NLS parameters are valid for specific SQL functions.


SQL Functions and Their Valid NLS Parameters


SQL Function Valid NLS Parameters

TO_DATE              NLS_DATE_LANGUAGE, NLS_CALENDAR


TO_NUMBER      NLS_NUMERIC_CHARACTERS, NLS_CURRENCY, NLS_DUAL_CURRENCY, NLS_ISO_CURRENCY,


TO_CHAR         NLS_DATE_LANGUAGE, NLS_NUMERIC_CHARACTERS, NLS_CURRENCY, NLS_ISO_CURRENCY, NLS_DUAL_CURRENCY, NLS_CALENDAR


TO_NCHAR         NLS_DATE_LANGUAGE, NLS_NUMERIC_CHARACTERS, NLS_CURRENCY, NLS_ISO_CURRENCY, NLS_DUAL_CURRENCY, NLS_CALENDAR


NLS_UPPER              NLS_SORT


NLS_LOWER                  NLS_SORT


NLS_INITCAP                 NLS_SORT


NLSSORT                          NLS_SORT


========




Tuesday, March 23, 2021

How to resolve issues while importing a DACPAC file during cloud migration which fails with kind of error Error SQL72014: or Error SQL72045: below:

 Many of the cloud migration issues are related to dacpac import for SQL server DB be it in Azure or AWS.


Below is one of them


*** Could not deploy package.

Error SQL72014: Core Microsoft SqlClient Data Provider: Msg 15007, Level 16, State 1, Line 1 'XYZReporting' is not a valid login or you do not have permission.

Error SQL72045: Script execution error.  The executed script:

CREATE USER [XYZReporting] FOR LOGIN [XYZReporting];


Just import the data with excluding the objects using the below switch:


PS C:\Program Files\Microsoft SQL Server\140\DAC\bin> .\sqlpackage /a:Publish /sf:C:\temp\db_test.dacpac /tsn:abxyz.cvvulhyblrdi.us-west-2.rds.amazonaws.com /tdn:demofin /tu:admin /tp:******  /p:ExcludeObjectTypes='Users;Logins;DatabaseRoles;RoleMembership;ServerRoleMembership;ServerRoles'

Wednesday, January 1, 2020

How to resolve ORA-12516 TNS:listener could not find available handler with matching protocol stack?

We see the error related to Listener refused the connection with following other error message as below:

ORA-12516,TNS:listener could not find available handler with matching protocol stack.
or 
Status:Failure -Test failed:IO Error: Got minus one from read call.

Usually we face the above error when number of process is not sufficient in the initialization parameter file to create additional connections.

Resolution:

Check the current process limit in V$parameter view

Connect to the correct DB or PDB if you are running 12c or above if it is a pluggable DB;

1. Alter system set processes=<new limit> scope=spfile;

2. Bounce the DB or PDB.

3. Restart the listener services.

Tuesday, December 17, 2019

How to resolve ORA-38824: A CREATE OR REPLACE command may not change the EDITIONABLE property of an existing object.

From Oracle version 12c onwards: -

Initially It seems the problem is related to Editioning feature of the object in the db is not enable or the schema user is not created with edition enable.
However it could be a related to container and non container DB.
This issue will not come where CDB=NO
In those cases where CDB=YES it will come and seems to be a bug.

Just Check:

SQL> select name,dbid,con_dbid,cdb,con_id from v$database;


NAME            DBID   CON_DBID CDB     CON_ID
--------- ---------- ---------- --- ----------
CDB1      1000678150 1000358150 YES          0


If CDB is yes means its a container DB and installer will fail.

 SQL> select name,dbid,con_dbid,cdb,con_id from v$database;

 NAME            DBID   CON_DBID CDB     CON_ID
--------- ---------- ---------- --- ----------
ORCLXYZ  74410566 2774410566 NO           0


If CDB is NO issue will not come.