Database configuration failed: Column Type does not allow DBNull Value

"Configuration wizard error "Database configuration failed: Column ‘Type’ does not allow DBNull Value". We need to allow nulls for the 'Type' column in SQL management studio for the User table.


Configuration Wizard ends with the following error:

Configuration Wizard error "Database configuration failed: Column ‘Type’ does not allow DBNull.Value"

CAUSE
Nulls for the 'Type' column for the ReportDefinitions table were not allowed in SQL Management Studio.

RESOLUTION
Run the following SQL query against the Current database:

update User set type = '' where type = NULL

for example, ‘0000-00-00’ works well with ‘Zero-date in NOT NULL Columns’ setting), or set destination column to NULL so you could process null data after the migration is complete.

update  User  set RENEWAL_DATE= '2000-01-01' where  RENEWAL_DATE = '0000-00-00'

update  User  set THE_TIME= '2000-01-01 00:00:00' where  THE_TIME= '0000-00-00 00:00:00'

Notes:

1. The '' is 2 single quotes one after the other and not a single double quote. The 2 single quotes back to back designates the query to enter a blank value in the field. If you use a double quote, the query will fail.
2. Make sure to backup your database before making direct changes to the database

SQL Server Row Count for all Tables in a Database

SQL server Syntax :

SELECT
     sOBJ.name AS [TableName]
      , SUM(sPTN.Rows) AS [RowCount]
FROM
      sys.objects AS sOBJ
      INNER JOIN sys.partitions AS sPTN
            ON sOBJ.object_id = sPTN.object_id
WHERE
      sOBJ.type = 'U'
      AND sOBJ.is_ms_shipped = 0x0
      AND index_id < 2 -- 0:Heap, 1:Clustered
 
GROUP BY
      sOBJ.schema_id
      , sOBJ.name
ORDER BY [TableName]
GO


Output : -

Get record counts for all tables in MySQL database

mySql  Syntax:


SELECT  table_rows,table_name
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'SchemaName' and table_rows<>0; --  Schemaname (Databasename)



output

Change Schema Name Of Table In SQL

I want to change schema name of table "Employees"in Database. In the current table Employees database schema name is "Schemaconverted" I want to change it to dbo.

Example:

FROM

Schemaconverted.Employees
TO

dbo.Employees


Syntax

ALTER SCHEMA dbo
    TRANSFER Schemaconverted.Employees

syntax  for All Base tables

use TestDB
go
declare @sql varchar(8000), @table varchar(1000), @oldschema varchar(1000), @newschema   varchar(1000)

  set @oldschema = 'Schemaconverted'
  set @newschema = 'dbo'

 while exists(select * from sys.tables where schema_name(schema_id) = @oldschema)

  begin
      select @table = name from sys.tables
      where object_id in(select min(object_id) from sys.tables where  schema_name(schema_id)  = @oldschema)

    set @sql = 'alter schema ' + @newschema + ' transfer ' + @oldschema + '.' + @table

   exec(@sql)
 end