SQL Server Stored Procedures – Fundamentals

Database Script to Create Tables for Exercises

ALTER TABLE [dbo].[USERDETAILS] DROP CONSTRAINT FK_USERDETAILS_USERLIST

GO

if exists (select * from sysobjects where id = object_id(N’[dbo].[USERDETAILS]‘) and OBJECTPROPERTY(id, N’IsUserTable’) = 1)
drop table [dbo].[USERDETAILS]

GO

if exists (select * from sysobjects where id = object_id(N’[dbo].[USERLIST]‘) and OBJECTPROPERTY(id, N’IsUserTable’) = 1)
drop table [dbo].[USERLIST]

GO

CREATE TABLE [dbo].[USERDETAILS] (
           [detail_id] [int] IDENTITY (1, 1) NOT NULL ,
           [usr_id] [int] NOT NULL ,
           [title] [varchar] (50) NULL ,
           [yrs_service] [numeric](18, 0) NULL ,
           [yrs_title] [numeric](18, 0) NULL

) ON [PRIMARY]

GO

CREATE TABLE [dbo].[USERLIST] (

            [usr_id] [int] IDENTITY (1, 1) NOT NULL ,
            [login] [varchar] (20) NOT NULL ,
            [pswd] [varchar] (20) NOT NULL ,
            [f_name] [varchar] (25) NULL ,
            [l_name] [varchar] (35) NOT NULL ,
            [address_1] [varchar] (30) NULL ,
            [address_2] [varchar] (30) NULL ,
            [city] [varchar] (30) NULL ,
            [state] [char] (2) NULL ,
            [zipcode] [char] (10) NULL ,            [email] [varchar] (50) NOT NULL

) ON [PRIMARY]

GO

About the Author

Tom O’Neill is a Senior Consultant in the Solutions/IDI practice at Deloitte & Touche. Tom’s areas of expertise center web application development, database architecture, and marketing automation. Tom O’Neill can be reached at 617-437-2945 or at tomoneill@deloitte.com.

Solutions/IDI, of Deloitte & Touche, is a technology consulting practice that provides various technology and IT-related services worldwide.

Published with the express written permission of the author. Copyright




Array

No comments yet... Be the first to leave a reply!

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |