ASP Alliance Member: MTG

NOTE: These scripts have been run and tested using SQL Server's Query Analyzer. They are compatible with both SQL Server 7.0 & 2000. You can Copy & Paste these scripts directly into SQL Server's Query Analyzer.

Scripts highlighted in ORANGE are compatible ONLY with SQL Server 2000

Practice these T-SQL Scripts Online - with my Online ASP Query Analyzer!

ADDing a Column to a Table
ADDing a DEFAULT CONSTRAINT to a Table
Adding a New Login
Adding a User to a Server Role
Adding an IDENTITY CONSTRAINT to an Existing Table
Adding an NT User Login using sp_grantlogin
Adding CHECK CONSTRAINTs to Tables
Adding DEFAULT CONSTRAINTs
Adding FOREIGN KEY CONSTRAINTs
Adding PRIMARY KEY CONSTRAINTs
Aggregate SELECTs must Use HAVING instead of WHERE
ALTERing a TABLE
ALTERing Stored Procedures
Assigning Values to Variables in an UPDATE Statement
Binding a DEFAULT to a Table Column
Binding a RULE to a Table Column
Changing a Login Password
Checking Database Consistency Between System Tables with DBCC CHECKCATALOG()
Checking Database Data Allocation Integrity with DBCC CHECKALLOC()
Checking for Table and Index Fragmentation with DBCC SHOWCONTIG()
Checking for the Existence of Values in Columns Using IN
Checking Inserted Values with CHECK CONSTRAINTs
Checking the Identity Integrity of a Table with DBCC CHECKIDENT()
Checking the Integrity of a Table with DBCC CHECKTABLE()
Checking the Structural Integrity of a Database with DBCC CHECKDB()
Control Flow Statements
Converting a Floating Number to a String with Decimal Places with STR()
Converting Data to Specific Data Types with CAST()
Converting Strings to Delimited Identifiers with QUOTENAME()
Converting Strings to Lowercase Characters with LOWER()
Converting Strings to Uppercase Characters with UPPER()
Copying a Table
Copying Tables with INSERT INTO
Correcting Inaccuracies in the sysindexes Table with DBCC UPDATEUSAGE()
Create CONTACTS Table
Creating a Stored Procedure that Rebuilds All Indexes in a Database
Creating a Stored Procedure
Creating an SPROC that checks for Table Fragmentation
Creating and Executing Stored Procedures Having the Same Name
Creating and Working with Unicode Data Types
Creating and Working with User-Defined Data Types
Creating Databases
Creating DEFAULTs
Creating Encrypted Stored Procedures
Creating INDEXes
Creating Multiple Column PRIMARY KEY CONSTRAINTs
Creating Multiple Instances of a String Into One with REPLICATE()
Creating Rows using Default Values
Creating RULEs
Creating Simple Procedures
Creating Tables
Creating Views
Creating, Using, and Removing Application Roles
Declaring and Using CURSORs
Delaying Execution with WAITFOR DELAY and TIME
Deleting a Table
Denying Statement Permissions to a DB User
Determining if a Role is a Member of a Database with IS_MEMBER()
Determining Whether a Login is a Member of a Server Role with IS_SRVROLEMEMBER()
Determining Whether the Current User has Access to a Given Database with HAS_DBACCESS()
Droping Remote Logins Using sp_droplinkedsrvlogin
Enabling Double-Quotes in T-SQL with SET QUOTED_IDENTIFIER
Enabling Stored Procedures on Remote Servers
Executing Command Prompt Statements with xp_cmdshell
Executing Stored Procedures on Remote Servers
Executing Stored Procedures
Exporting Tables to Text Files with BCP and xp_cmdshell
Generating String Spaces with SPACE()
Granting a User Read-only Access to Databases
Granting Access Rights to Objects
Granting Access to an NT Domain User Account
Granting Database Access to Users
Granting DB Access to a Login using sp_grantdbaccess
Granting Statement Permissions to a DB User
Improving Performance by Keeping Tables Cached in Memory with DBCC PINTABLE()
Joining Recordsets from Multiple Tables with OUTER JOINs
Linking Server with sp_addlinkedserver and sp_addlinkedsrvlogin
Linking Servers with sp_addlinkedserver and sp_addlinkedsrvlogin
Looping through Statements with WHILE
Mail Stored Procedures
Obtaining a Database Filename with FILE_NAME()
Obtaining a Database Username with USER_NAME()
Obtaining a File Group ID with FILEGROUP_ID()
Obtaining a File Group Name with FILEGROUP_NAME()
Obtaining a List of Object Dependencies with sp_depends
Obtaining a Stored Procedure ID with @@PROCID
Obtaining a Substring with LEFT()
Obtaining a Substring with RIGHT()
Obtaining a Substring with SUBSTRING()
Obtaining a User Database ID with USER_ID()
Obtaining a User Login ID with SUSER_ID()
Obtaining a User Login Name from a Security ID with SUSER_SNAME()
Obtaining a User Login Name with SUSER_NAME()
Obtaining a User Login Security ID with SUSER_SID()
Obtaining an Object Name Using OBJECT_NAME()
Obtaining Column Properties Using COLUMNPROPERTY()
Obtaining Current Locks Using SP_LOCK
Obtaining Currently Linked Servers Using sp_linkedservers
Obtaining Database IDs using DB_ID()
Obtaining Database Object IDs Using OBJECT_ID()
Obtaining Database Properties Using DATABASEPROPERTY()
Obtaining DB File Properties with FILEPROPERTY()
Obtaining DBCC Syntax Information
Obtaining Full-Text Catalog Properties with FULLTEXTCATALOGPROPERTY()
Obtaining Full-Text Service Properties with FULLTEXTSERVICEPROPERTY()
Obtaining Index Properties with INDEXPROPERTY()
Obtaining Indexed Columns with INDEX_COL()
Obtaining Info about all Databases in SQL Server with sp_helpdb
Obtaining Object Properties with OBJECTPROPERTY
Obtaining Stored Procedure Information with sp_stored_procedure
Obtaining the Application Name for the Current Session with APP_NAME()
Obtaining the ASCII Number of a Character
Obtaining the Average Value of Table Columns with AVG()
Obtaining the Character Representaion of an ASCII Number
Obtaining the Current Database Username with USER
Obtaining the Day from a Date with DAY()
Obtaining the File ID of a Database Using FILE_ID()
Obtaining the First Occurance of a Non-null Expression in a Set of Expressions with COALESCE()
Obtaining the Length of a Column with COL_LENGTH()
Obtaining the Length of a String with LEN()
Obtaining the Maximum Values from Table Columns with MAX()
Obtaining the Minimum Value from Table Columns with MIN()
Obtaining the Month of a Date Using MONTH()
Obtaining the Name of a Database using DB_NAME()
Obtaining the Name of a Table Column with COL_NAME()
Obtaining the Number of Table Columns with COUNT()
Obtaining the Oldest Active Database Transaction with DBCC OPENTRAN()
Obtaining the Properties of a File Group with FILEGROUPPROPERTY()
Obtaining the Results of Whether Two Strings Sound the Same or Not with DIFFERENCE()
Obtaining the SET Options that are Active for the Current Connection
Obtaining the Sum of all Column Values with SUM()
Obtaining the Unicode Number of a Character
Obtaining the YEAR() from a Date
Obtaining Unique SELECT Results with DISTINCT
Raising Errors Using RAISERROR
Rebuilding ALL Database Indexes in Every Table
Rebuilding All Table Indexes with DBCC DBREINDEX()
Rebuilding all Table Indexes
Rebuilding Indexes Using DBCC DBREINDEX
Reenabling Previously Disabled CONSTRAINTs (CHECK)
Removing CONSTRAINTs from Tables
Removing Trailing and Leading Spaces with LTRIM() and RTRIM()
Reparing a Database Quickly with DBCC CHECKDB()
Replacing Lengths of Text with Other Text with STUFF()
Replacing Search Text with Other Text with REPLACE()
Retrieving Records from a Table Referencing Another with INNER JOINs
Retrieving Records Records from Multiple Tables with FULL JOINs
Retrieving the Results of Multiple Tables with UNIONs
Returning Values from Stored Procedures with RETURN
Reversng a String with REVERSE()
Revoking DB Access to a Login using sp_revokedbaccess
Revoking Statement Permissions to a DB User
Revoking User Access to Databases
Searching for Text in a String with CHARINDEX()
Searching for Text in a String with PATINDEX()
Selecting a Range of Values with BETWEEN
SETting ANSI_NULLs
Setting Collation Compatibility
Setting ISOLATION Levels
Setting the DEADLOCK_PRIORITY
Setting the Maximum Number of User Connections with sp_configure
Shrinking a Database with DBCC SHRINKDATABASE()
Shrinking Database Data and Log Files with DBCC SHRINKFILE()
Stored Procedure Parameters
Temporarily Disabling CONSTRAINTs (NOCHECK)
Unbinding a DEFAULT from a Table
Unbinding a Rule from a Table Column
Updating Records with Locks
User and Login Administration
Using Aliases
Using ALTER TABLE
Using AND, OR, and NOT
Using CASE WHEN Statements
Using Configuration Variables
Using DATEADD
Using DATEDIFF
Using DATENAME
Using DATEPART
Using Distributed Queries
Using GO for Batch Execution
Using IF ELSE Statements
Using INFORMATION_SCHEMA
Using Inner Joins by using the WHERE Clause
Using NULLs
Using OUTPUT Parameters in Stored Procedures
Using Remote Queries with OPENQUERY
Using Remote Queries with OPENROWSET
Using SELECT Table Aliases
Using STDEV
Using STDEVP()
Using Subqueries with EXISTS
Using the Alias AS
Using the Global IDENTITY Variable
Using Transaction Statements
Using VAR()
Using Variables
Using VARP
Using Wildcards with LIKE
Viewing the Contents of a Stored Procedure
[SQL Server 2000 ONLY] Creating and Using Single-Valued Functions
[SQL Server 2000 ONLY] Creating and Using User-Defined Single-Query Functions
[SQL Server 2000 ONLY] Creating Virtual Tables
[SQL Server 2000 ONLY] Obtaining the Number of Affected Rows with ROWCOUNT_BIG()
[SQL Server 2000 ONLY] Using Multiple Instances of SQL Server 7.0 and 2000
Total: 204

more comming soon... I only have two hands...

Comments & Questions Form Comment(s):