Add IDENTITY or Unique number to a table – T/SQL

I want to update my table records. However, my existing table does not have any unique columns. So, I need to append Identity column and update all the records based on that Identity column. For example, If my temp table has 1000 records without any unique column values. I need to number all these 1000 records and update the values. There is no need to do an UPDATE. The identity column is going to be populated when it is created….

Read More

MSSQL – Query to Find Column From All Tables of Database

How many tables in the database AdventureWorks have column names like ‘EmployeeID’? It was quite an interesting question and I thought if there are scripts that can do this would be great. I quickly wrote down the following script which will go return all the tables containing specific columns along with their schema name.

Read More

SQL Time Zone Conversion Functions

I did a lot of research on Microsoft’s website, and the only time zone conversion functions that I could find were relative to analysis services, and would not provide me with a solution to my problem. So, my options were to pay someone to modify our application, or create a process in the database environment to perform the conversions as data is inserted into the common database environment.

Read More

How to Restore a MySQL Database with Command Line

In MySQL, you can use the mysql command to restore the database from a dump file. mysqldump is a command-line utility used to generate a MySQL logical database backup as a single .sql file with a set of SQL statements. The utility helps you dump MySQL tables, multiple databases, or their objects. Keep in mind that it is not possible to back up MySQL databases or data to separate .sql files with the mysqldump utility Step 1: Create the database In the command prompt,…

Read More

Posted in MySQL, SQL Tagged , Comments Off on How to Restore a MySQL Database with Command Line
Illegal mix of collations (utf8_general_ci,IMPLICIT) – MySQL

— Preparations SET SESSION group_concat_max_len=4294967295; SET SESSION foreign_key_checks=0; — Set default character sets and collations ALTER DATABASE dwdata CHARACTER SET utf8 COLLATE utf8_general_ci; ALTER DATABASE dwnotification CHARACTER SET utf8 COLLATE utf8_general_ci; ALTER DATABASE dwsystem CHARACTER SET utf8 COLLATE utf8_general_ci; ALTER DATABASE dwworkflowengine CHARACTER SET utf8 COLLATE utf8_general_ci; — Create statement that adjusts collations for tables in dwdata SELECT GROUP_CONCAT(CONCAT(‘ALTER TABLE ‘, TABLE_SCHEMA, ‘.’, TABLE_NAME, ‘ CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;’) SEPARATOR ‘\n’) AS “Prepared statement” FROM INFORMATION_SCHEMA.TABLES…

Read More

Posted in MySQL Tagged , , Comments Off on Illegal mix of collations (utf8_general_ci,IMPLICIT) – MySQL
How to Change Schema of Mssql Tables, Stored Procedures, and Views All at the Same Time

Sometimes changing database users changes who the database owner is for a table from dbo.tablename to badschema.tablename. This will also affect any stored procedures created under the tables with the incorect schema.  If you need to change the schema for these tables back to the default dbo schema follow the steps below. Open Microsoft SQL Server Management Studio and log in. Click the New Query button. Paste the following script into the New Query box changing oldschema to the name of the current schema:…

Read More

What is – DML, DDL, DCL and TCL – Introduction

DML DML is abbreviation of Data Manipulation Language. It is used to retrieve, store, modify, delete, insert and update data in database. Examples: SELECT, UPDATE, INSERT statements DDL DDL is abbreviation of Data Definition Language. It is used to create and modify the structure of database objects in database. Examples: CREATE, ALTER, DROP statements DCL DCL is abbreviation of Data Control Language. It is used to create roles, permissions, and referential integrity as well it is used to control access to database…

Read More

Posted in SQL Tagged Comments Off on What is – DML, DDL, DCL and TCL – Introduction
Find Duplicate Values with SQL Script

Let’s assume that data has been entered in a table and now you want to find if a table column have duplicated values. Here is an SQL script to join data from one table for comparison purpose. The table must have a primary key column (unique column) and the second column for comparison. Here is a sample script: Details: – DWDOCID is the primary key column – AIXFlag, is just a fixed value (optional) – SAMPLEEN is the table…

Read More