Toronto SQL

First I set the column of REC to a datatype of bigint . Then i run this query to do a count of records:

DECLARE @count bigint
SET @count = 0
UPDATE (table name) SET @count = rec = @count + 1

This should fill the whole row starting at 1 and counting up by 1 each record.

-------------

Then I went into the Design Table routine from Enterprise Manager and changed the REC column to a datatype of CHAR(15).

Closed Enterprise manager and went back into Query Analyzer and entered this query:

update (table name) set rec = REPLACE(STR(rec,15), SPACE(1), '0')

We will eventually turn this into a tutorial, but here it is for those who will just copy/paste.

create database MYDATABASE
on (name=MYDATABASE,
filename='c:\CCTB111\MYDATABASE.mdf',
size=1mb,
maxsize=2mb)
log on (name=MYDATABASE_log,
filename='c:\CCTB111\MYDATABASE.ldf',
size=1mb,
maxsize=2mb)

--create the tables according to your blueprints

use MyClassExamples
create table Student --always start with your parent table
(StudentID char(10) NOT NULL primary key,
FName varchar(50) not null,
Address varchar(50) null,
Postal_code varchar(7),
sex char(1),

Search column names for keywords using "like"

SELECT name FROM sysobjects
WHERE id IN (
SELECT id FROM syscolumns WHERE name like '%KEYWORD%' )

Microsoft SQL query to return all tables, row counts, column counts, and size of tables.

USE MYDATABASE
GO
CREATE TABLE #temp (
table_name sysname ,
row_count INT,
reserved_size VARCHAR(50),
data_size VARCHAR(50),
index_size VARCHAR(50),
unused_size VARCHAR(50))
SET NOCOUNT ON
INSERT #temp
EXEC sp_msforeachtable 'sp_spaceused ''?'''
SELECT a.table_name,
a.row_count,
COUNT(*) AS col_count,
a.data_size
FROM #temp a
INNER JOIN information_schema.columns b
ON a.table_name collate database_default
= b.table_name collate database_default

Scenario

Version: MySQL 5.0
Database Name: testdb
Table Name: domains
Field Name: domainame

Find all domain names that contain any digit 0 to 9.

SELECT *
FROM `domains`
WHERE `domainame` LIKE '%0%'
OR `domainame` LIKE '%1%'
OR `domainame` LIKE '%2%'
OR `domainame` LIKE '%3%'
OR `domainame` LIKE '%4%'
OR `domainame` LIKE '%5%'
OR `domainame` LIKE '%6%'
OR `domainame` LIKE '%7%'
OR `domainame` LIKE '%8%'
OR `domainame` LIKE '%9%'
OR `domainame` LIKE '%-%'

Scenario

Version: MySQL 5.0
Database Name: testdb
Table Name: domains
Field Name: domainame

Example

Show all domain names that do not contain any digit from 0 to 9.
SELECT * FROM `domains` WHERE `domainame` NOT LIKE '%0%'
and `domainame` NOT LIKE '%1%'
and `domainame` NOT LIKE '%2%'
and `domainame` NOT LIKE '%3%'
and `domainame` NOT LIKE '%4%'
and `domainame` NOT LIKE '%5%'
and `domainame` NOT LIKE '%6%'
and `domainame` NOT LIKE '%7%'
and `domainame` NOT LIKE '%8%'
and `domainame` NOT LIKE '%9%'

Version: MySQL 5.0
Database Name: testdb
Table Name: domains
Field Name: domainame

Simple Select Query #1

Show all domain names that do not contain a zero digit.

SELECT * FROM `domains` WHERE `domainame` NOT LIKE '%0%'