Three Ways To Get The Time Portion Of A Datetime Value

This popped up in a newsgroup today so I decided to do a little post about it. How do you get the time information only from a datetime value
These are at least 3 ways to do this

1) use the 3 different datepart functions and concatenate them
2) convert and grab the 8 right most characters
3) convert to varchar and use style 108

So here we go

--1 use the 3 different datepart functions and concatenate them
SELECT CONVERT(VARCHAR(2),DATEPART(HH,GETDATE())) + ':' +
CONVERT(VARCHAR(2),DATEPART(MI,GETDATE())) + ':' +
CONVERT(VARCHAR(2),DATEPART(S,GETDATE()))

--2 convert and grab the 8 right most characters
SELECT RIGHT(CONVERT(VARCHAR(22),GETDATE(),120),8)


--3 convert to varchar and use style 108
SELECT CONVERT(VARCHAR(12),GETDATE(),108)

Visual Studio 2005 Team Edition for Database Professionals Interview On Channel 9

Channel 9 has a cool webcast with several people talking about Visual Studio 2005 Team Edition for Database Professionals (Data Dude)

From the site: "Cameron Skinner, Gert Drapers, Robert Merriman, Thomas Murphy, and Matt Nunn sat down me with to discuss a new edition to the Visual Studio Team product family: Visual Studio 2005 Team Edition for Database Professionals. We talked about why this product was created and Cameron (the product unit manager) gave a walk through of the current CTP release. Targeted for delivery at the end of the year, you can grab the CTP bits and learn more at the team’s website: http://msdn.microsoft.com/vstudio/teamsystem/dbpro/"

You can download the webcast here (http://channel9.msdn.com/Showpost.aspx?postid=212797) or click on the picture

Visual Studio 2005 Team Edition for Database Professionals CTP 4 Available

Visual Studio 2005 Team Edition for Database Professionals (Data Dude) CTP 4 is available for download

Brief Description
Community Technology Preview (CTP) 4
Tools for building SQL databases in a managed project environment with support for versioning, deployment, unit testing, refactoring, and off-line SQL development.

Overview
Tools for building SQL databases in a managed project environment with support for versioning, deployment, unit testing, refactoring, and off-line SQL development. This release is focused on completing a full functionality scenario for SQL 2000 and includes an updated project system with reverse engineering, a new SQL Query Editor, Schema and Data Compare, Data Generation, Unit Testing, and Rename Refactoring. This release does not have functionality changes from CTP3, only stability work and the new project UI.

Get it here

SqlBlog

Peter DeBetta and Adam Machanic have created SqlBlog.com. It has a very nice list of SQL Server blogs and if you click on roller you will see a about 40 of them displayed (mine is missing ;-( )
So what are you waiting for? Sign up here, I already did

Non Updating Update Performance Improvement In SQL Server 2005

When you do a non updating update like the one below

UPDATE t
SET i = 1
WHERE i=1



In SQL 2000, the lack of the optimization leads to updating the nonclustered index even if the value is not changing.

In the SQL 2005 plan, it is possible to appreciate
- a “Compute Scalar” operator that compares the current value and new value of the column being modified
- a new filter operator that on a row by row basis will determine whether the nonclustered index is being affected or not
- the fact that nonclustered index maintenance is now bypassed

Read the whole article (including statistics profile output screenshot) at the Tips, Tricks, and Advice from the SQL Server Query Optimization Team blog

Setting Identity Value Back To 1 After Deleting All Rows From A Table

Sometimes you want the identity value to start from 1 again after you delete all the rows from a table
There are 2 ways to accomplish that
1 Use the TRUNCATE TABLE command instead of DELETE
2 If you do use DELETE then run DBCC CHECKIDENT and reseed the table

Here is some code to explain what I mean

CREATE TABLE TestValues (Id INT identity,DateVale DATETIME)
INSERT INTO TestValues VALUES(GETDATE())
INSERT INTO TestValues VALUES(GETDATE())
INSERT INTO TestValues VALUES(GETDATE())
INSERT INTO TestValues VALUES(GETDATE())
INSERT INTO TestValues VALUES(GETDATE())

SELECT *
FROM TestValues

-- This is the output
-- 1 2006-07-10 12:31:17.050
-- 2 2006-07-10 12:31:17.050
-- 3 2006-07-10 12:31:17.050
-- 4 2006-07-10 12:31:17.050
-- 5 2006-07-10 12:31:17.050


DELETE TestValues

INSERT INTO TestValues VALUES(GETDATE())

SELECT *
FROM TestValues

-- Output
-- 6 2006-07-10 12:31:29.143


TRUNCATE TABLE TestValues

INSERT INTO TestValues VALUES(GETDATE())

SELECT *
FROM TestValues

-- Output
-- 1 2006-07-10 12:31:38.317


INSERT INTO TestValues VALUES(GETDATE())

DELETE TestValues

DBCC CHECKIDENT(TestValues,RESEED,0)
-- Message
-- Checking identity information: current identity value '2', current column value '0'.
-- DBCC execution completed. If DBCC printed error messages, contact your system administrator.

INSERT INTO TestValues VALUES(GETDATE())

SELECT *
FROM TestValues

-- Output
-- 1 2006-07-10 12:31:52.503

DROP TABLE TestValues

Building SQL Server Integration Services Packages

Learn how easy it is to build and debug your first SQL Server Integration Services (SSIS) Package. Donald Farmer introduces the development environment and the basic concepts of packages and walks through the creation and testing of a simple data flow. See why users describe SSIS as the most productive environment for data integration.

Watch the video at MSDN TV

Get the files here

Designing Effective Aggregations In Analysis Services 2005

If you're into Analysis Services, Integration Services, Reporting Services and BI in general - you'll want to check out Elizabeth Vitt's new blog I found the link to her blog on Kimberly Tripps blog

In her first entry, Elizabeth Vitt highlights the design components that determine which attributes are considered for aggregation:


Aggregation Definition
Attribute Relationships
Aggregation Candidates
Aggregation Usage

Read the article here: Influencing Aggregation Candidates

Free Quest LiteSpeed For SQL Server Developer Edition

Here is an Independence Day gift for you. You can get the fast backup and recovery solution LiteSpeed for free (to be used with the developer edition only)

From the Quest site:

"The fast backup and recovery solution for Microsoft SQL Server Developer Edition is here - LiteSpeed™ for SQL Server Developer Edition. LiteSpeed's low-impact, high-performance compression technology allows you to dramatically reduce storage costs and backup/recovery windows.




With LiteSpeed, you'll achieve:

Fast backup and recovery. LiteSpeed's technology achieves 50 percent faster backup and recovery times, allowing you to cut your restore time in half.
Reduced file sizes and storage costs. LiteSpeed's compression technology compresses data up to 95 percent, saving disk space.
Optimized backup and recovery. You'll maintain complete control, while backup and recovery performance is improved right out of the box.



Reduce the time and costs associated with backup and recovery projects today with this full-version download. LiteSpeed for SQL Server Developer Edition - a $45 value - is completely FREE to SQL Server Central readers for a limited time"

Get it here

DDL Triggers And Events

I promised I would write a 'real' SQl subject so here it is: DDL triggers and DDL events for use with DDL triggers

Let's start by creating 2 simple triggers

USE AdventureWorks
GO

CREATE TRIGGER ddlTestEvents1
ON DATABASE
FOR DROP_TABLE, ALTER_TABLE, CREATE_TABLE
AS
PRINT 'You must disable Trigger "ddlTestEvents1" to drop,create or alter tables!'
ROLLBACK;
GO

CREATE TRIGGER ddlTestEvents2
ON DATABASE
FOR DDL_TABLE_EVENTS
AS
PRINT 'You must disable Trigger "ddlTestEvents2" to drop, create or alter tables!'
ROLLBACK;
GO


Let's try creating a table
CREATE TABLE wasabi(id INT)
GO

And here is the error message, the first trigger fired
You must disable Trigger "ddlTestEvents1" to drop,create or alter tables!
Msg 3609, Level 16, State 2, Line 1
The transaction ended in the trigger. The batch has been aborted.

Let's disable the ddlTestEvents1 trigger and see what happens
DISABLE TRIGGER [ddlTestEvents1] ON DATABASE
GO

Let's try creating a table again
CREATE TABLE wasabi(id INT)
GO

Aha, same error message(almost) different trigger
You must disable Trigger "ddlTestEvents2" to drop, create or alter tables!
Msg 3609, Level 16, State 2, Line 1
The transaction ended in the trigger. The batch has been aborted.


so basically the event DDL_TABLE_EVENTS is the same as the DROP_TABLE, ALTER_TABLE, CREATE_TABLE events. To check that run the following code


SELECT name,te.type_desc,te.type
FROM sys.triggers t
JOIN sys.trigger_events te on t.object_id = te.object_id
WHERE t.parent_class=0
AND name IN('ddlTestEvents1','ddlTestEvents2')
ORDER BY name,te.type_desc

The ouput is the same for both triggers

name...........,type_desc,type
ddlTestEvents1,ALTER_TABLE,22
ddlTestEvents1, CREATE_TABLE,21
ddlTestEvents1, DROP_TABLE, 23
ddlTestEvents2, ALTER_TABLE, 22
ddlTestEvents2, CREATE_TABLE, 21
ddlTestEvents2, DROP_TABLE, 23

So when you use DDL_TABLE_EVENTS in the trigger, then 3 events will be in the sys.trigger_events catalog view. This is all fine but how do you find out these things? I can't find DDL_TABLE_EVENTS anywhere. Also I know that DDL_LOGIN_EVENTS is the same as CREATE LOGIN, ALTER LOGIN and DROP LOGIN combined but where is this documented?

Script out the ddlDatabaseTriggerLog trigger (this is the only trigger that is in the AdventureWorks database when you install SQL server), you will see a DDL_DATABASE_LEVEL_EVENTS event
when you run the following query (same as before, different trigger name) you will see that it returns 76 rows (basically all events)

SELECT name,te.type_desc,te.type
FROM sys.triggers t
JOIN sys.trigger_events te on t.object_id = te.object_id
WHERE t.parent_class=0
AND name ='ddlDatabaseTriggerLog'
ORDER BY name,te.type_desc


I am sure I will edit this (many times)
But first I will list all the events
You will see the events followed by a grouped event in red for example
CREATE_TABLE
ALTER_TABLE
DROP_TABLE
DDL_TABLE_EVENTS

I will have to go through all these events and use the sys.trigger_events to find this info out before I post this (unless someone points to in the right direction where to find this)

DDL Statements with Database Scope:
CREATE_APPLICATION_ROLE (Applies to CREATE APPLICATION ROLE statement and sp_addapprole. If a new schema is created, this event also triggers a CREATE_SCHEMA event.) ALTER_APPLICATION_ROLE (Applies to ALTER APPLICATION ROLE statement and sp_approlepassword.)
DROP_APPLICATION_ROLE (Applies to DROP APPLICATION ROLE statement and sp_dropapprole.)

CREATE_ASSEMBLY
ALTER_ASSEMBLY
DROP_ASSEMBLY
-- should be DDL_ASSEMBLY_EVENTS but who knows?


ALTER_AUTHORIZATION_DATABASE (Applies to ALTER AUTHORIZATION statement when ON DATABASE is specified, and sp_changedbowner.)

CREATE_CERTIFICATE
ALTER_CERTIFICATE
DROP_CERTIFICATE

CREATE_CONTRACT
DROP_CONTRACT

GRANT_DATABASE
DENY_DATABASE
REVOKE_DATABASE

CREATE_EVENT_NOTIFICATION
DROP_EVENT_NOTIFICATION

CREATE_FUNCTION
ALTER_FUNCTION
DROP_FUNCTION

CREATE_INDEX
ALTER_INDEX
DROP_INDEX

CREATE_MESSAGE_TYPE
ALTER_MESSAGE_TYPE
DROP_MESSAGE_TYPE

CREATE_PARTITION_FUNCTION
ALTER_PARTITION_FUNCTION
DROP_PARTITION_FUNCTION

CREATE_PARTITION_SCHEME
ALTER_PARTITION_SCHEME
DROP_PARTITION_SCHEME

CREATE_PROCEDURE
ALTER_PROCEDURE
DROP_PROCEDURE

CREATE_QUEUE
ALTER_QUEUE
DROP_QUEUE

CREATE_REMOTE_SERVICE_BINDING
ALTER_REMOTE_SERVICE_BINDING
DROP_REMOTE_SERVICE_BINDING

CREATE_ROLE (Applies to CREATE ROLE statement, sp_addrole, and sp_addgroup.)
ALTER_ROLE
DROP_ROLE (Applies to DROP ROLE statement, sp_droprole, and sp_dropgroup.)

CREATE_ROUTE
ALTER_ROUTE
DROP_ROUTE

CREATE_SCHEMA (Applies to CREATE SCHEMA statement, sp_addrole, sp_adduser, sp_addgroup, and sp_grantdbaccess.)
ALTER_SCHEMA (Applies to ALTER SCHEMA statement and sp_changeobjectowner.)
DROP_SCHEMA

CREATE_SERVICE
ALTER_SERVICE
DROP_SERVICE

CREATE_STATISTICS
DROP_STATISTICS
UPDATE_STATISTICS

CREATE_SYNONYM
DROP_SYNONYM

CREATE_TABLE
ALTER_TABLE
DROP_TABLE
DDL_TABLE_EVENTS

CREATE_TRIGGER
ALTER_TRIGGER
DROP_TRIGGER

CREATE_TYPE (Applies to CREATE TYPE statement and sp_addtype.)
DROP_TYPE (Applies to DROP TYPE statement and sp_droptype.)

CREATE_USER (Applies to CREATE USER statement, sp_adduser, and sp_grantdbaccess.)
ALTER_USER
DROP_USER (Applies to DROP USER statement, sp_dropuser, and sp_revokedbaccess.)

CREATE_VIEW
ALTER_VIEW
DROP_VIEW

CREATE_XML_SCHEMA_COLLECTION
ALTER_XML_SCHEMA_COLLECTION
DROP_XML_SCHEMA_COLLECTION

DDL Statements with Server Scope:
ALTER_AUTHORIZATION_SERVER

CREATE_DATABASE
ALTER_DATABASE
DROP_DATABASE

CREATE_ENDPOINT
ALTER_ENDPOINT
DROP_ENDPOINT

CREATE_LOGIN (Applies to CREATE LOGIN statement, sp_addlogin, sp_grantlogin, xp_grantlogin, and sp_denylogin when used on a nonexistent login that must be implicitly created.)
ALTER_LOGIN (Applies to ALTER LOGIN statement, sp_defaultdb, sp_defaultlanguage, sp_password, and sp_change_users_login when Auto_Fix is specified.)
DROP_LOGIN (Applies to DROP LOGIN statement, sp_droplogin, sp_revokelogin, and xp_revokelogin.)
DDL_LOGIN_EVENTS

GRANT_SERVER
DENY_SERVER
REVOKE_SERVER

And like I said I will edit this after I run my tests

Top 5 Posts For June 2006

Below are the top 5 posts according to Google Analytics for the month of June

COALESCE And ISNULL Differences is number one, the big reason for this is that SQL Server Magazine linked to it


Here are the posts in order by pageviews descending

COALESCE And ISNULL Differences
OPENROWSET And Excel Problems
Login failed for user 'sa'. Reason: Not associated with a trusted SQL Server connection. SQL 2005
Split a comma delimited string fast!
SQL Query Optimizations

And I promise I will have some real SQL code later today

Top SQL Server Google Searches For June 2006

These are the top SQL Searches on this site for the month of June. I have left out searches that have nothing to do with SQL Server or programming. Here are the results...

dbreindex microsoft sqlserver forum
mysql grant privileges column level
site:sqlservercode.blogspot.com union
-310 sql error
"SQL Server Everywhere"
Collation
replace last two characters
grant update mysql syntax
"The provider ran out of memory"
money
-310 sql
how to sql to excel
Msg 2627, Level 14, State 1
ADD_DAYS oracle syntax
query for SQL bit checking

List DDL Triggers By Using The sys.triggers Catalog View

How do you list DDL triggers in SQL Server 2005? It's very easy you just query the sys.triggers catalog view. Let's try an example

USE adventureworks
GO

--Create our trigger
CREATE TRIGGER trDropTable
ON DATABASE
FOR DROP_TABLE
AS
PRINT 'You can not drop tables'
ROLLBACK
GO


--Create the table
CREATE TABLE abc(id INT)
GO

--Let's drop the table
DROP TABLE abc
GO

Our trigger works because the following message is displayed after we tried to drop the table

You can not drop tables
Msg 3609, Level 16, State 2, Line 1
The transaction ended in the trigger. The batch has been aborted.

In order to list DDL triggers you need to use parent_id = 0
SELECT * FROM sys.triggers
WHERE parent_id = 0

In order to list DML triggers you need to use parent_id <> 0
SELECT * FROM sys.triggers
WHERE parent_id <> 0

And if you want to list all triggers you can just ommit the WHERE clause

Murphy's Law And SQL Server

Why do things break the moment that the person who handles something is on vacation?
Here is the full story. A co-worker goes on vacation, he has his work on a specific server, this server is in our lab and not on the MCN (Mission Critical Network). First let me tell you that we are considered ‘renegades’ and that we don’t get operation support for some of our machines because we don’t use corporate standards and we need full control over our machines.
Anyway the server worked fine for 2 years, co-worker goes on vacation to Japan and the very next day I am getting crazy errors like can not use ad-hoc queries use linked server instead???? I click on the linked server and get OLEDB FoxPro driver missing errors? Fine let’s restart, simple enough right? Wrong! After hitting stop from Service Manager the status is ‘stopping’, after 20 minutes it is still ‘stopping’
Mmm I don’t want to to a hard reboot because I can corrupt all the databases and I don’t even know where half of the backups are (I don’t use this server). Okay let’s try NET STOP MSSQLSERVER. Nope application is in transition and can not be stopped error. Then it occurred to me that maybe we got hit by the slammer worm. Since it’s stopping I can not execute serverproperty so I right click on the sqlserver.exe file and look up the version number. It’s 2039, good that’s SP4. Then I hit restart from the start button and it restarted and everything was fine.

So what is Murphy's Law? Here is the Wikipedia link in case you want to know
http://en.wikipedia.org/wiki/Murphy's_law

DELETE and Subquery (IN) Problem

When you have a DELETE used with an IN (subquery) all rows will be deleted if your subquery is wrong
What do I mean by this? Let's say you have a table named Table1 with 1 column, the name of that column is Col1
When you do SELECT ID FROM Table1 you get an error.
However when you do
DELETE FROM TEST1 WHERE ID IN
(SELECT ID FROM Table1)
all rows are deleted


Let's start with our test

--Create tables and data
CREATE TABLE TEST1 (ID INT)
GO
CREATE TABLE TEST2 (BLA INT)
GO

INSERT INTO TEST1 VALUES(1)
INSERT INTO TEST1 VALUES(2)
INSERT INTO TEST1 VALUES(3)
INSERT INTO TEST2 VALUES(1)
GO

-- The following will raise an error
SELECT ID FROM TEST2 WHERE BLA = 1
GO
--Server: Msg 207, Level 16, State 3, Line 1
--Invalid column name 'ID'.


-- The following statement will delete the entire table
DELETE FROM TEST1 WHERE ID IN
(SELECT ID FROM TEST2 WHERE BLA = 1)
GO
--(3 row(s) affected)


--One way of doing it
DELETE FROM TEST1 WHERE ID IN
(SELECT B.ID FROM TEST2 B WHERE BLA = 1)
--Server: Msg 207, Level 16, State 3, Line 1
--Invalid column name 'ID'.


--Another way
DELETE FROM TEST1 WHERE exists
(SELECT * FROM TEST2 B WHERE BLA = 1 AND b.ID =TEST1.ID)
--Server: Msg 207, Level 16, State 3, Line 1
--Invalid column name 'ID'.

The Difference Between Unique Constraint And Unique Index

Do you know the the difference between an unique constraint, and an unique index?
If you do not or if you are not sure then make sure you read the following article:
Unique constraint, or unique index

That article also has a link on how to do selective uniqueness handling

SQL Server Datetime Datatypes

Since I am kind of busy (Work, House, FIFA World Cup Soccer/Football) I said that I would post links to great SQL Server stuff by other people, so here is a gem that you should definitely read if you have any questions about dates in SQL Server. The name of this page is "The ultimate guide to the datetime datatypes" and it is written by SQL Server MVP Tibor Karaszi and it covers the following things.


Date and time datatypes in SQL Server
Date and time formats
Date and time formats for input
Recommendations for input
Warnings and common misconceptions
Output of datetime values
Searching for datetime values
Getting rid of the time portion
Tip: Always set the time to the same value
Other tips, from SQL Server MVP Steve Kass
Why is 1753 the earliest date for datetime?
References and reading tips

So what are you waiting for, click on the link below?

http://www.karaszi.com/SQLServer/info_datetime.asp

3.5 Ways To Show Stored Procedure Code In SQL Server 2005

There are four (more on that later) ways in SQL Server 2005 to get the create procedure script. Why does the title say 3.5, well that is because the INFORMATION_SCHEMA.ROUTINES view only returns the first 4000 characters. So if your proc is 5000 characters then you are out of luck (not really since you can use the other 3 methods)

So let’s start, we will be using the uspGetBillOfMaterials stored procedure in the AdventureWorks database

sp_helptext
This is the same as in SQL server 2000 nothing new here

sp_helptext 'dbo.uspGetBillOfMaterials'



sys.sql_modules
So this is a new view in SQL Server 2005, what we need is the definition column

SELECT definition
FROM sys.sql_modules
WHERE object_id = OBJECT_ID('dbo.uspGetBillOfMaterials')



OBJECT_DEFINITION()
This is a new function in SQL Server 2005, just combine it with OBJECT_ID to get the proc code back

SELECT OBJECT_DEFINITION (OBJECT_ID('dbo.uspGetBillOfMaterials'))



INFORMATION_SCHEMA.ROUTINES
This is the problematic one, if the ROUTINE_DEFINITION is greater than 4000 characters then it will be truncated

SELECT ROUTINE_DEFINITION
FROM INFORMATION_SCHEMA.ROUTINES
WHERE SPECIFIC_NAME ='uspGetBillOfMaterials'
AND SPECIFIC_SCHEMA= 'dbo'

5 Reasons I Will Blog A Little Less The Next 6 Weeks

Because of these 5 reasons
A) I just moved into a new house and I still have a lot of things to do
B) My wife is 33 weeks pregnant with twins and I have to help her a lot, we also have a son who is 2 (sometimes he acts as if he is in his terrible two’s but mostly not)
C) I still have a lot of things to do to prepare for the babies (mostly buying stuff)
D) I also have a ‘real’ job and we are launching some new products soon
E) It’s the FIFA 2006 World Cup (soccer), I have 2 passports (Dutch and Croatian, but I live in the US) so I have to watch at least all the games when one of my countries is playing

I will be posting a little less of my own content but I will link to other great posts
So today I would like to point out to you a great series of posts about query plans and query executions. I found this blog (http://blogs.msdn.com/craigfr/) after reading Louis Davidson’s blog (http://drsql.spaces.msn.com/)

Here are the posts and they are in FIFO order
The Building Blocks of Query Execution
Viewing Query Plans
Properties of Iterators

Use OBJECTPROPERTY To Generate A List Of Object Types

How do you query the sysobjects system table and get the object type back for every single object
You can use the type and xtype columns, these contain the following data

xtype
Object type. Can be one of these object types:
C = CHECK constraint
D = Default or DEFAULT constraint
F = FOREIGN KEY constraint
L = Log
FN = Scalar function
IF = Inlined table-function
P = Stored procedure
PK = PRIMARY KEY constraint (type is K)
RF = Replication filter stored procedure
S = System table
TF = Table function
TR = Trigger
U = User table
UQ = UNIQUE constraint (type is K)
V = View
X = Extended stored procedure


type
Object type. Can be one of these values:
C = CHECK constraint
D = Default or DEFAULT constraint
F = FOREIGN KEY constraint
FN = Scalar function
IF = Inlined table-function
K = PRIMARY KEY or UNIQUE constraint
L = Log
P = Stored procedure
R = Rule
RF = Replication filter stored procedure
S = System table
TF = Table function
TR = Trigger
U = User table
V = View
X = Extended stored procedure

Or you can use OBJECTPROPERTY. OBJECTPROPERTY is better in my opinion because you can see right away what you are looking for
For example OBJECTPROPERTY ( id , 'IsUserTable' ) is much easier to understand than type = 'u'

Bu using CASE with OBJECTPROPERTY we can generate a nice report

SELECT name,CASE
WHEN OBJECTPROPERTY ( id , 'IsSystemTable' ) =1 THEN 'System Table'
WHEN OBJECTPROPERTY ( id , 'IsProcedure' ) =1 THEN 'Procedure'
WHEN OBJECTPROPERTY ( id , 'IsPrimaryKey' ) =1 THEN 'Primary Key'
WHEN OBJECTPROPERTY ( id , 'IsDefault' ) =1 THEN 'Default'
WHEN OBJECTPROPERTY ( id , 'IsForeignKey' ) =1 THEN 'Foreign Key'
WHEN OBJECTPROPERTY ( id , 'IsCheckCnst' ) =1 THEN 'Check Constraint'
WHEN OBJECTPROPERTY ( id , 'IsView' ) =1 THEN 'View'
WHEN OBJECTPROPERTY ( id , 'IsConstraint' ) =1 THEN 'Constraint'
WHEN OBJECTPROPERTY ( id , 'IsTrigger' ) =1 THEN 'Trigger'
WHEN OBJECTPROPERTY ( id , 'IsScalarFunction' ) =1 THEN 'Scalar Function'
WHEN OBJECTPROPERTY ( id , 'IsTableFunction' ) =1 THEN 'Table Valued Function'
WHEN OBJECTPROPERTY ( id , 'IsRule' ) =1 THEN 'Rule'
WHEN OBJECTPROPERTY ( id , 'IsExtendedProc' ) =1 THEN 'Extended Stored Procedure'
WHEN OBJECTPROPERTY ( id , 'IsUserTable' ) =1 THEN 'User Table'
END ObjectType, *
FROM sysobjects

And of course there are a bunch of INFORMATION_SCHEMA views that you can use to get some of the same information back

SELECT * FROM INFORMATION_SCHEMA.CHECK_CONSTRAINTS
SELECT * FROM INFORMATION_SCHEMA.CONSTRAINT_TABLE_USAGE
SELECT * FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS
SELECT * FROM INFORMATION_SCHEMA.TABLES
SELECT * FROM INFORMATION_SCHEMA.VIEWS
SELECT * FROM INFORMATION_SCHEMA.COLUMNS