/*
Author: Michael Thomas 
email: michael@michael-thomas.com

Used for the examples at: www.michael-thomas.com for MSSQL Commands.
Section:  Tables.

Need to add:
* Constraints

*/
use master
IF NOT EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = N'MyTempDB') begin
  create database [MyTempDB]
  -- DROP DATABASE [MyTempDB]
END ELSE BEGIN
  print 'DB already exists'
END
go

use MyTempDB  

-- ***********************
--Create the table
-- ***********************
if exists (select * from dbo.sysobjects where id = object_id(N'MyTableCreateDrop') ) Begin
  drop table MyTableCreateDrop
End
create table MyTableCreateDrop ( myInt int, myVarchar varchar(10) )
select count(*) from dbo.sysobjects where name = 'MyTableCreateDrop'
--If the table exists, drop it.
if exists (select * from dbo.sysobjects where id = object_id(N'MyTableCreateDrop') ) Begin
  drop table MyTableCreateDrop
End
--See if it still exists.
select count(*) from dbo.sysobjects where name = 'MyTableCreateDrop'

-- ***********************
-- Temp Tables
-- ***********************
create table #tmptable ( myInt int, myvarchar varchar(50) )
go
-- Temp tables are NOT stored in the dbo.sysobjects table !!!
select count(*) from dbo.sysobjects where name = '#tmptable'  -- 0
insert into #tmptable values (1,'Hello World 1')
select * from #tmptable
drop table #tmptable
go

-- ***********************
-- See information about Tables.
-- ***********************

-- Select information about an object via the name field.
select * from dbo.sysobjects where name = N'MyTable' -- via name field.
-- Select information about an object via the object id.
select * from dbo.sysobjects where id = object_id(N'MyTable') -- Using the object id.
-- List all objects in the table.
select type, name, id, crdate, refdate, '|' as '|', * from dbo.sysobjects 
-- List all objects created by Users (not system)
select type, name, id, crdate, refdate, '|' as '|', * from dbo.sysobjects where type = 'U'