Category Archives: Database

MySQL backup Script

The following script returns a SQL query with all your database data structure and data.

if (!function_exists(‘mysql_dump’)) {

function mysql_dump($database) {

$query = ”;

$tables = @mysql_list_tables($database);
while ($row = @mysql_fetch_row($tables)) { $table_list[] = $row[0]; }

for ($i = 0; $i < @count($table_list); $i++) { $results = mysql_query('DESCRIBE ' . $database . '.' . $table_list[$i]); $query .= 'DROP TABLE IF EXISTS `' . $database . '.' . $table_list[$i] . '`;' . lnbr; $query .= lnbr . 'CREATE TABLE `' . $database . '.' . $table_list[$i] . '` (' . lnbr; $tmp = ''; while ($row = @mysql_fetch_assoc($results)) { $query .= '`' . $row['Field'] . '` ' . $row['Type']; if ($row['Null'] != 'YES') { $query .= ' NOT NULL'; } if ($row['Default'] != '') { $query .= ' DEFAULT \'' . $row['Default'] . '\''; } if ($row['Extra']) { $query .= ' ' . strtoupper($row['Extra']); } if ($row['Key'] == 'PRI') { $tmp = 'primary key(' . $row['Field'] . ')'; } $query .= ','. lnbr; } $query .= $tmp . lnbr . ');' . str_repeat(lnbr, 2); $results = mysql_query('SELECT * FROM ' . $database . '.' . $table_list[$i]); while ($row = @mysql_fetch_assoc($results)) { $query .= 'INSERT INTO `' . $database . '.' . $table_list[$i] .'` ('; $data = Array(); while (list($key, $value) = @each($row)) { $data['keys'][] = $key; $data['values'][] = addslashes($value); } $query .= join($data['keys'], ', ') . ')' . lnbr . 'VALUES (\'' . join($data['values'], '\', \'') . '\');' . lnbr; } $query .= str_repeat(lnbr, 2); } return $query; } } [/sourcecode]

Linq Update FK – Operation is not valid due to the current state of the object

sql serverTo update the entity in question you need to get a new entity for it from the context rather than setting it directly.

Wrong Way

OrderRecord.customerId = 105;

Throws an error!
“Operation is not valid due to the current state of the object”

Solution

OrderRecord.tblCustomer = db.tblCustomers.Single(t => t.customerID == 105);

Returns no error!

Enable TSQL xp_cmdshell command

SQL Server blocked access to procedure ‘sys.xp_cmdshell’ of component ‘xp_cmdshell’ because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of ‘xp_cmdshell’ by using sp_configure

A simple script to enable xp_cmdshell in SQL Server

sp_configure 'show advanced options',1
GO
RECONFIGURE
GO
sp_configure xp_cmdshell,1
GO
RECONFIGURE

Count Tables, Views and Stored Procedures in you Database

sql server
I posted some useful scripts to count your database’s Tables, Views and Stored Procedures.

Count Tables

SELECT COUNT(*) AS 'Tables Count' FROM sys.objects WHERE TYPE ='U' AND name NOT LIKE 'Sy%'
SELECT name AS 'Tables Name' FROM sys.objects WHERE TYPE ='U' AND name NOT LIKE 'Sy%'

Count Views

SELECT COUNT(*) AS 'Views Count'  FROM sys.objects WHERE TYPE ='V' AND name NOT LIKE 'Sy%'
SELECT name AS 'View Name' FROM sys.objects WHERE TYPE ='V' AND name NOT LIKE 'Sy%'

Count Stored Procedures

SELECT COUNT(*) AS 'Stored Procs Count' FROM sys.objects WHERE TYPE ='P' AND name NOT LIKE 'Sy%'
SELECT name AS 'Stored Procs Name' FROM sys.objects WHERE TYPE ='P' AND name NOT LIKE 'Sy%'

Check for Database Constraints

sql server

Problem
Adding constraints such as check constraints or foreign keys to a table are best practices to keep your data as clean as possible with minimal data enforcement rules performed at the database level. Unfortunately sometimes issues may occur where the data becomes out of synch and one of these constraints has been violated. This may be due to disabled constraints or constraints that are later added with the NOCHECK option. Finding these issues can be done by running queries to check each of the constraints, but is there any easier way to determine if the data the constraints support has been violated?

Solution
As mentioned already, one approach would be to write queries for each of the constraints and check the data to see if the constraints are being enforced. This is probably not all that difficult, but it could be time consuming. Another approach to tackle this issue is to use the DBCC CHECKCONSTRAINTS command. This command allows you to check the constraints to ensure that no data is violating the constraints that have been setup.

This command can be run as follows:

DBCC CHECKCONSTRAINTS (TableName) - checks an individual table
DBCC CHECKCONSTRAINTS (ConstraintName) - checks an individual constraint
DBCC CHECKCONSTRAINTS WITH ALL_CONSTRAINTS - checks all constraints in the database
DBCC CHECKCONSTRAINTS WITH ALL_ERRORMSGS - returns all rows that violate constraints
DBCC CHECKCONSTRAINTS WITH NO_INFOMSGS - suppress messages when query runs

SQL Server, Empty your Database & Reset Identity Columns **made Simple

sql server

This is a SQL Script that Cleans your Database Records & resets Identity Columns, and it is all in 6 lines!

/*Disable Constraints &amp; Triggers*/
exec sp_MSforeachtable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'
exec sp_MSforeachtable 'ALTER TABLE ? DISABLE TRIGGER ALL'
/*Perform delete operation on all table for cleanup*/
exec sp_MSforeachtable 'DELETE ?'
/*Enable Constraints &amp; Triggers again*/
exec sp_MSforeachtable 'ALTER TABLE ? CHECK CONSTRAINT ALL'
exec sp_MSforeachtable 'ALTER TABLE ? ENABLE TRIGGER ALL'
/*Reset Identity on tables with identity column*/
exec sp_MSforeachtable 'IF OBJECTPROPERTY(OBJECT_ID(''?''), ''TableHasIdentity'') = 1 BEGIN DBCC CHECKIDENT (''?'',RESEED,0) END'

If you dont want to miss any saved Diagrams… Continue reading SQL Server, Empty your Database & Reset Identity Columns **made Simple

MySQL Import Data From EXCEL (csv file)

mysql logo

1. Save as excel document in CSV format. (found as CSV Comma Delimited file)

2. Connect to Mysql through command line
Start Cmd
cd c:\mysql\bin\ (or wherever the mysql directory is installed…)

3. Connect to MySQL command console
use

mysql -u root

to connect as root user to be able to do changes to your db

4. Remember to Save the CSV File in the mysql\bin directory

5. Enter the following commands
SELECT THE DATABASE TO USE

use yourdbname;

ENTER THE INSERT COMMAND

LOAD DATA LOCAL INFILE '/yourCSVfilename.csv'
INTO TABLE test_table
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
(field1, filed2, field3); 

For Greek or non-unicode Remember 3 Things
1.  mysql> set character_set_database =utf8;
2. CSV File saved as UTF8 encoding
3. database Table character set to greek_unicode_ci;

SQL Script for Constrains

sql server

This is a SQL Script that returns a list of all constrains in a database..

SELECT f.name AS ForeignKey,
	OBJECT_NAME(f.parent_object_id) AS TableName,
	COL_NAME(fc.parent_object_id, fc.parent_column_id) AS ColumnName,
	OBJECT_NAME (f.referenced_object_id) AS ReferenceTableName,
	COL_NAME(fc.referenced_object_id, fc.referenced_column_id) AS ReferenceColumnName
FROM sys.foreign_keys AS f
	INNER JOIN sys.foreign_key_columns AS fc ON f.OBJECT_ID = fc.constraint_object_id

T-SQL Settings Explained

A list of widely used SQL settings Explained

  • ANSI NULLS
  • QUOTED_IDENTIFIER
  • ARITHMABORT
  • ANSI_DEFAULTS
  • ANSI_WARNINGS
  • DATEFIRST
  • DATEFORMAT
  • NOCOUNT
  • NOEXEC
  • IDENTITY_INSERT
  • IMPLICITY_TRANSACTIONS
  • LANGUAGE

ANSI NULLS
– Syntax

SET ANSI_NULLS {ON | OFF}

– Explanation

The SQL-92 standard requires that an equals (=) or not equal to (<>) comparison against a null value evaluates to FALSE. When SET ANSI_NULLS is ON, a SELECT statement using WHERE column_name = NULL returns zero rows even if there are null values in column_name. A SELECT statement using WHERE column_name <> NULL returns zero rows even if there are nonnull values in column_name.

When SET ANSI_NULLS is OFF, the Equals (=) and Not Equal To (<>) comparison operators do not follow the SQL-92 standard. A SELECT statement using WHERE column_name = NULL returns the rows with null values in column_name. A SELECT statement using WHERE column_name <> NULL returns the rows with nonnull values in the column.

Continue reading T-SQL Settings Explained