Using SQL case statement in order by clause http://ping.fm/BLlDK

Use of sql Partition by clause together with case and count

Wherever you are trying to use aggregate functions in sql server, it will give you error like that a group by clause is necessary. Yes, if you really want to group by, then ok. But what about if you want to see the count of a group but you want to every elements of that group. Group by wont allow you to see other columns that what is specified in the group by clause. But you can. Here is the tips and tricks.


select Class, StudentID, StudentName, count(Class) over (partition by Class)
from StudentTable

The benefit of partition by is that you can use partition by for generating as many columns as you want and you can specify a different partition criteria for every column.

See below how partition by is used together with the case statement. Here one single column is generated but it is using two different partition by clause, one to determine the case condition and the other is generating the row number value.

select Class,
case count(Class) over (partition by Class)
when 1 then null
else Row_Number() over (partition by Class order by Class)
end,
StudentID,
StudentName
from StudentTable


The output will appear like this:

You can find some other useful cases where partition by can be used in the following article.

http://www.programmingsolution.net/sqlserver2005/sqlserver-solutions/partition-by.php

create PROCEDURE Insert_Top as
 
   /*
 
   CREATE TABLE [dbo].[top](
      [SPID] [float] NULL,
      [Status] [nvarchar](255) NULL,
      [Login] [nvarchar](255) NULL,
      [HostName] [nvarchar](255) NULL,
      [BlkBy] [nvarchar](255) NULL,
      [DBName] [nvarchar](255) NULL,
      [Command] [nvarchar](255) NULL,
      [CPUTime] [float] NULL,
      [DiskIO] [float] NULL,
      [LastBatch] [nvarchar](255) NULL,
      [ProgramName] [nvarchar](255) NULL,
      [SPID1] [float] NULL,
      [REQUESTID] [float] NULL,
      [ModifiedDate] [datetime] NULL CONSTRAINT [DF_top_ModifiedDate]  DEFAULT (getdate())
   ) ON [PRIMARY]
 
   */
 
 
   INSERT INTO [DPart_P008_Dev].[dbo].[top]
             (
      [SPID],
      [Status],
      [Login],
      [HostName],
      [BlkBy],
      [DBName],
      [Command],
      [CPUTime],
      [DiskIO],
      [LastBatch],
      [ProgramName],
      [SPID1],
      [REQUESTID])
    EXEC sp_who2

Now, create the loop procedure:

    create PROCEDURE Loop_Insert_Top as
    WHILE (1=1) BEGIN
        EXEC insert_top
        WAITFOR DELAY '00:01:00';
    END

To view the records:

   select * from [top]

Use of sql Partition by clause together with case and count

Wherever you are trying to use aggregate functions in sql server, it will give you error like that a group by clause is necessary. Yes, if you really want to group by, then ok. But what about if you want to see the count of a group but you want to every elements of that group. Group by wont allow you to see other columns that what is specified in the group by clause. But you can. Here is the tips and tricks.


select Class, StudentID, StudentName, count(Class) over (partition by Class)
from StudentTable

The benefit of partition by is that you can use partition by for generating as many columns as you want and you can specify a different partition criteria for every column.

See below how partition by is used together with the case statement. Here one single column is generated but it is using two different partition by clause, one to determine the case condition and the other is generating the row number value.

select Class,
case count(Class) over (partition by Class)
when 1 then null
else Row_Number() over (partition by Class order by Class)
end,
StudentID,
StudentName
from StudentTable


The output will appear like this:

You can find some other useful cases where partition by can be used in the following article.

http://www.programmingsolution.net/sqlserver2005/sqlserver-solutions/partition-by.php

EXECUTE sp_executesql

DECLARE @IntVariable int;
DECLARE @SQLString nvarchar(500);
DECLARE @ParmDefinition nvarchar(500);
DECLARE @max_title varchar(30);

SET @IntVariable = 197;
SET @SQLString = N'SELECT @max_titleOUT = max(Title)
FROM AdventureWorks.HumanResources.Employee
WHERE ManagerID = @level';
SET @ParmDefinition = N'@level tinyint, @max_titleOUT varchar(30) OUTPUT';

EXECUTE sp_executesql @SQLString, @ParmDefinition, @level = @IntVariable, @max_titleOUT=@max_title OUTPUT;
SELECT @max_title;

Here @SQLString is the dynamic query where you have 2 variables --- @level and @max_titleOUT. The variable @level is going into the dynamic query and @max_titleOUT is coming out from the dynamic query.

@ParamDefinition is the string where we must declare all the variables that will be used in the dynamic query. Look that the @max_titleOUT is set as output.

After that we have to mention which variable outside the dynamic query will give the value for @level and which variable outside the dynamic query will hold the value from @max_titleOUT. So we are assigning

@level = @IntVariable, @max_titleOUT=@max_title OUTPUT

Where @IntVariable and @max_title is the variable declared outside dynamic query. So @level will take the value from @IntVariable and will be used inside the dynamic query.
@max_title is the variable where we will get the value out from the dynamic query.

Pretty tuff huh, but no way. This is a very advanced feature of sql server. Hope you like this.

Convert Exponential varchar to numeric:

select convert(numeric(18, 3),cast('1.50721e+006' as float))
select convert(numeric(18, 3),cast('1.50721e+006' as float))
select cast('1/05/07' as datetime) , but in this case casting is usually not required, autocast. But to convert datetime into varchar, casting must be done.
select convert(varchar, getdate(), 103)  01/01/2008


select function_name(params_comma_seperated)

select top 1400 * from gkam_midas_data
select top 1400 * from gkam_midas_data

rtrim()
ltrim()
left(str, 4)
right(str, 4)


declare @var_name int
declare @var_name table( @id int, @name varchar(20) )

Nested case when:
update #Pre_Final set [PI]=
Case when RegionID is null and CountryID is null then
Case When EvaluationIndex >= 100 then 1
When EvaluationIndex < 100 then -1 else -2 End
when RegionID is not null and CountryID is null then
Case When RegionEvaluationIndex >= 100 then 1
When RegionEvaluationIndex < 100 then -1 else -2 End
when RegionID is not null and CountryID is not null then
Case When CountryEvaluationIndex >= 100 then 1
When CountryEvaluationIndex < 100 then -1 else -2 End
End


if @period_type IN ('QTR','YTD','MAT')
select @period = dbo.GET_PERIOD_FROM_DATE('Quarter', @max_data_date)
Else if @period_type = 'YEAR' --or @period_type = 'YTD'
select @period = dbo.GET_PERIOD_FROM_DATE('Year',@max_data_date)
Else --if @period_type != 'YEAR' and @period_type = 'QTR'
select @period = dbo.GET_PERIOD_FROM_DATE('Quarter',@max_data_date)


Bulk Update Query:

UPDATE [TEMP] SET [temp].COUNTRYID = B.COUNTRY FROM
(SELECT REGION, COUNTRY FROM RAW_DATA)B
WHERE [temp].REGIONID = [B].REGION

Here RegionID must uniquely identify a row. If not then some unique id must be matched in the where clause.

Take one value from a sql in a variable:
select @a=accountID from gkam_performance_data where accountID=1



DECLARE @order int
SET @order = 3
SELECT * FROM dbo.ICD10_DIAGNOSIS_temp
ORDER BY
CASE WHEN @order = 1 THEN ICD10_DIAG_CD END DESC,
CASE WHEN @order = 2 THEN PAR_ICD10_DIAG_CD END ASC,
CASE WHEN @order = 3 THEN ICD10_DIAG_CD_LVL_NBR END DESC,
CASE WHEN @order = 4 THEN ICD10_DIAG_DESC END ASC

DECLARE @order int
SET @order = 3
SELECT * FROM dbo.ICD10_DIAGNOSIS_temp
ORDER BY
CASE WHEN @order = 1 THEN ICD10_DIAG_CD
WHEN @order = 2 THEN PAR_ICD10_DIAG_CD
WHEN @order = 3 THEN ICD10_DIAG_CD_LVL_NBR
WHEN @order = 4 THEN ICD10_DIAG_DESC
END
ASC

Remember dynamic SQL (creating SQL in a string and then executing) cannot be effectively cached so this is a much better option.

Find duplicates
SELECT column,COUNT(*) c FROM table
GROUP BY column HAVING COUNT(*) > 1

Looping with GO

CREATE TABLE #TEST (ID INT IDENTITY (3,1), ROWID uniqueidentifier)
CREATE TABLE #TEST2 (ID INT IDENTITY (1,3), ROWID uniqueidentifier)
GO
INSERT INTO #TEST (ROWID) VALUES (NEWID())
INSERT INTO #TEST2 (ROWID) VALUES (NEWID())
GO 100



SET @columnList = 'CustomerID, ContactName, City'
SET @city = 'London'
SET @sqlCommand = 'SELECT ' + @columnList + ' FROM customers WHERE City = @city'
EXECUTE sp_executesql @sqlCommand, N'@city nvarchar(75)', @city = @city



Retrieve tons of information (length, data type, name, etc.) of columns for a table. Works in (T|MS) SQL (all I've tested it in).

select * from information_schema.columns
where table_name = 'mhplt_level'

IF NOT EXISTS
(
SELECT * FROM [information_schema].[columns]
WHERE table_name = 'Customer'
AND table_schema = 'dbo'
AND column_name = 'FavoriteColorId'
)
BEGIN
ALTER TABLE [dbo].[Customer]
ADD FavoriteColorId int



Receiving a table output from a sql:
insert @atc4_list
exec sp_executesql @v_dynsql, @v_dynsql_params, @dyn_tg_id = @p_tg_id, @dyn_a_market = @v_a_market



SELECT OBJECT_ID,
OBJECT_NAME(OBJECT_ID) AS TABLENAME,
NAME AS COLUMNNAME,
SEED_VALUE,
INCREMENT_VALUE,
LAST_VALUE,
IS_NOT_FOR_REPLICATION
FROM SYS.IDENTITY_COLUMNS ORDER BY 2

DBCC CHECKIDENT('mhplt_objective',RESEED,1)

Here are some useful tips for sql server. These tips are taken
from the following link

http://souptonuts.sourceforge.net/mssql_tutorial_tips.htm


TIP 1:

Need to know how much fragmentation?

    DBCC SHOWCONTIG (tbl_transaction)

There is also a more advanced option

    DBCC SHOWCONTIG (tbl_transaction) WITH TABLERESULTS, ALL_LEVELS

There is also a defrag command

    DBCC INDEXDEFRAG(Northwind,Customers,1)



TIP 2:


This is a cool feature:

   IF object_id('OutputTest') IS NOT NULL
   BEGIN
      drop table OutputTest
   END
 
 
   CREATE TABLE OutputTest
       (
           ID int NOT NULL,
           Description varchar(max) NOT NULL,
       )
 
       INSERT INTO OutputTest (ID, Description) VALUES (1, 'row 1')
       INSERT INTO OutputTest (ID, Description) VALUES (2, 'row 2')
       INSERT INTO OutputTest (ID, Description) VALUES (3, 'row 3')
 
   DECLARE @DeleteLog AS TABLE (LogID INT, LogEntry VARCHAR(MAX))
 
       DELETE OutputTest
       OUTPUT DELETED.ID, DELETED.Description INTO @DeleteLog
       WHERE ID = 1
 
       SELECT *into DeleteLog FROM @DeleteLog
 
 
   select * from DeleteLog



TIP 3:

   Standard table create statement, with getdate() for automatically
   recording the time when a record was added.

     CREATE TABLE [junk23](
      [JunkID] [smallint] IDENTITY(1,1) NOT NULL,
      [Name] varchar(20) NOT NULL,
      [ModifiedDate] [datetime] NOT NULL   DEFAULT (getdate()))

 
 
TIP 4:

   create PROCEDURE Insert_Top as
 
   /*
 
   CREATE TABLE [dbo].[top](
      [SPID] [float] NULL,
      [Status] [nvarchar](255) NULL,
      [Login] [nvarchar](255) NULL,
      [HostName] [nvarchar](255) NULL,
      [BlkBy] [nvarchar](255) NULL,
      [DBName] [nvarchar](255) NULL,
      [Command] [nvarchar](255) NULL,
      [CPUTime] [float] NULL,
      [DiskIO] [float] NULL,
      [LastBatch] [nvarchar](255) NULL,
      [ProgramName] [nvarchar](255) NULL,
      [SPID1] [float] NULL,
      [REQUESTID] [float] NULL,
      [ModifiedDate] [datetime] NULL CONSTRAINT [DF_top_ModifiedDate]  DEFAULT (getdate())
   ) ON [PRIMARY]
 
   */
 
 
   INSERT INTO [DPart_P008_Dev].[dbo].[top]
             (
      [SPID],
      [Status],
      [Login],
      [HostName],
      [BlkBy],
      [DBName],
      [Command],
      [CPUTime],
      [DiskIO],
      [LastBatch],
      [ProgramName],
      [SPID1],
      [REQUESTID])
    EXEC sp_who2

Now, create the loop procedure:

    create PROCEDURE Loop_Insert_Top as
    WHILE (1=1) BEGIN
        EXEC insert_top
        WAITFOR DELAY '00:01:00';
    END

To view the records:

   select * from [top]




TIP 5:

 sp_help
 sp_helptext Insert_Page7_into_Combined_sproc


 "All that really happens when you create a procedure is that its
  syntax is checked and its source code is inserted into the
  syscomments system table"




TIP 6:

  Getting a directory listing of the c:\ drive.


      -- To allow advanced options to be changed.
      EXEC sp_configure 'show advanced options', 1
      GO
      -- To update the currently configured value for advanced options.
      RECONFIGURE
      GO
      -- To enable the feature.
      EXEC sp_configure 'xp_cmdshell', 1
      GO
      -- To update the currently configured value for this feature.
      RECONFIGURE
      GO

      EXEC master..xp_cmdshell "dir c:\*"



TIP  7:

      select
          'Name'          = o.name,
          'Owner'         = user_name(ObjectProperty( object_id, 'ownerid')),
          'Object_type'   = substring(v.name,5,31)

      --into t_sp_help
      from sys.all_objects o, master.dbo.spt_values v
      where o.type = substring(v.name,1,2) collate database_default and v.type = 'O9T'
      and substring(v.name,5,31) ='stored procedure'
      and user_name(ObjectProperty( object_id, 'ownerid'))='dbo'
      order by [Owner] asc, Object_type desc, Name asc



TIP 8:

 You may need indexes. Try running the following query to see if the
 value is above 5000.


      SELECT *
      FROM
      (SELECT user_seeks * avg_total_user_cost * (avg_user_impact * 0.01) AS index_advantage,
      migs.* FROM sys.dm_db_missing_index_group_stats migs) AS migs_adv
        INNER JOIN sys.dm_db_missing_index_groups AS mig ON migs_adv.group_handle =
         mig.index_group_handle
        INNER JOIN sys.dm_db_missing_index_details AS mid ON mig.index_handle = mid.index_handle
      ORDER BY migs_adv.index_advantage




TIP 9:

  Need to see what users are doing?

       select * from sys.dm_exec_sessions




TIP 10:

  Need the last day of the month?


       Select
           dateadd(mm,1,convert(datetime,convert(char(10),getdate(),110)) -
              day(getdate())+1)-1




TIP 11:

  Put last day of month into a user defined function.


      create FUNCTION dbo.GetLastDayOfMonth()
      RETURNS datetime
      AS
      BEGIN
        DECLARE  @OutDate datetime
          set @OutDate = (Select dateadd(mm,1,convert(datetime,convert(char(10),getdate(),110))
             - day(getdate())+1)-1)
      RETURN(@OutDate)
      END

  Now call it:

      select dbo.GetLastDayOfMonth()




TIP 12:

  Need a function that gets the date a year ago at the beginning of
  the month?


      create FUNCTION dbo.Get1YearAgo()
      RETURNS datetime
      AS
      BEGIN
        DECLARE  @OutDate datetime
        set @OutDate = (Select dateadd(mm,-12,convert(datetime,convert(char(10),
                   getdate(),110)) - day(getdate())+1)
      )
        RETURN(@OutDate)
      END

      select dbo.Get1YearAgo()




TIP 13:

  Need to get a list of all stored procedures?

      SELECT name
      FROM dbo.sysobjects
      WHERE type = 'P' AND category = 0




TIP 14:

   Need to get the median?  Also see TIP 15 below and TIP 19.

    
       SELECT Median=AVG(c1) FROM (
         SELECT MIN(c1) AS c1 FROM (
         SELECT TOP 50 PERCENT col AS c1 FROM table
        ORDER BY c1 DESC) t
          UNION ALL
        SELECT MAX(c1) FROM (
        SELECT TOP 50 PERCENT col AS c1 FROM table
        ORDER BY c1) t
        ) M


  (Also reference: http://www.sqlmag.com/Article/ArticleID/49827/49827.html)
   But note, you can probably use the sp_executesql statement as well.


   Here's a way to get the 75%.



      WITH T AS
      (
        SELECT [Sls Rep],[Prod No],[UOM],[Unit Sales $],
            ROW_NUMBER() OVER(PARTITION BY [Sls Rep],[Prod No],[UOM]
               ORDER BY [Unit Sales $]) AS RowNum,
                COUNT(*) OVER(PARTITION BY [Sls Rep],[Prod No],[UOM]) AS Cnt
                  FROM Pg7_all_trans
                  )
                  SELECT [Sls Rep],[Prod No],[UOM],Avg([Unit Sales $]) [Median Unit Sales $],
                  Max(RowNum) [RowNum], Max(Cnt) [Cnt] into Pg7_75
                  FROM T
                  WHERE RowNum >= 0.75*(Cnt+1) and RowNum <= 0.75*(Cnt+2)                    GROUP BY [Sls Rep],[Prod No],[UOM]    TIP 15:     sp_executesql -- Executing the contents of a string as a SQL statement.          Declare @S nvarchar(500);         set @S = 'select * from' + ' junk'         execute sp_executesql @S     But wait! You can do this in a sproc:                 create proc select_table(@a as nvarchar(50))         as         Declare @t as nvarchar(500);         set @t = 'select * from ' + @a;         execute sp_executesql @t     Now just run this as follows:          select_table 'junk';      Here's another example with parameters:         EXECUTE sp_executesql          N'SELECT * FROM AdventureWorks.HumanResources.Employee          WHERE ManagerID = @level',          N'@level tinyint',          @level = 109;     Here's an example with quotes:         Declare @S nvarchar(max);        set @S = '        IF object_id(''junk'') IS NOT NULL        BEGIN           DROP TABLE junk        END               create table        junk (a int,        b varchar(20));        insert into junk (a,b) values (1,''one'');        insert into junk (a,b) values (2,''two'');        select * from junk;        '        -- print @S        execute sp_executesql @S       (Also reference: http://msdn2.microsoft.com/en-us/library/ms188001.aspx)    TIP 16:     More sp_executesql --            DECLARE @sql AS NVARCHAR(MAX), @i AS INT;        SET @sql = N'';        SET @i = 1;        WHILE @i <= 100        BEGIN          SET @sql = @sql + N'PRINT ' + CAST(@i AS NVARCHAR(10))           + NCHAR(13) + NCHAR(10);          SET @i = @i + 1;        END        EXEC sp_executesql @sql;    TIP 17:     Simple example of a union statement:         select  *  into t1_t2_combined from        (select * from t1             union          select * from t2) as a    TIP 18:     Need to find SQL version?         SELECT @@VERSION     or                SELECT  SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'),              SERVERPROPERTY ('edition')        Reference:         http://support.microsoft.com/kb/321185    TIP 19:     Example calculating Median on the table Median_table                                GO                SET ANSI_NULLS ON                GO                SET QUOTED_IDENTIFIER ON                GO                               alter FUNCTION [dbo].[calc_median_product_uom] (@product as nvarchar(30),                @uom as nvarchar(4)                )                -- Created:  Mike Chirico 
              -- Updated: Thu Sep 27 16:30:20 EDT 2007
              --
              -- Example Usage:
              --
              -- select product,uom,DPart_P008.dbo.calc_median_product_uom(product,uom) Median from Median_table
              --
              --
            
            
              RETURNS money
              AS
              BEGIN
            
              DECLARE  @Median as money
            
              SELECT @Median=AVG(c1) FROM (
                SELECT MIN(c1) AS c1
                 FROM (SELECT TOP 50 PERCENT AvqPrice AS c1 FROM Median_table
                   where product=@product and uom=@uom ORDER BY c1 DESC
              ) as t
              UNION ALL
              select max(c1) as c1 from (
                SELECT TOP 50 PERCENT AvqPrice AS c1 FROM
                  Median_table where product=@product and uom=@uom ORDER BY c1
              ) as t
              ) M
            
            
                RETURN(@Median)
              END




TIP 20:

  Example listing all the indexes on table mytable.

         select a.name table_name,
                b.name index_name,
                d.name column_name,
                c.index_column_id
           from sys.tables a,
                sys.indexes b,
                sys.index_columns c,
                sys.columns d
         where a.object_id = b.object_id
          and b.object_id = c.object_id
          and b.index_id  = c.index_id
          and c.object_id = d.object_id
          and c.column_id = d.column_id
          and a.name      = 'mytable'



TIP 21:

  Need a comparison between Oracle 10g and SQL 2005? Take a look at
  the following:

      http://www.wisdomforce.com/dweb/resources/docs/MSSQL2005_ORACLE10g_compare.pdf



TIP 22:

  Need to free up space?  The following query shows how much space
  can be freed.


         SELECT name ,size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS AvailableSpaceInMB
         FROM sys.database_files;

  To free up space from the database 'mydatabase' use the following
  command:

         DBCC SHRINKDATABASE('mydatabase',0);

  To free up sapce from the logfile 'mylogfile' use the following command:

         DBCC SHRINKFILE('mylogfile',0);



TIP 23:

  Getting the Sum, Max across rows in a table.

     Assume a simple table defined as follows:

     create table junk
     (a int,
      b int,
      c int);

     insert into junk (a,b,c) values (1,4,3);
     insert into junk (a,b,c) values (2,5,9);
     insert into junk (a,b,c) values (3,8,2);


      select *, (select sum(my_columns)
      from (select a as my_columns
         union all
            select b
         union all
           select c) as t) as sum_column
     from junk



Tip 24:

  Delete all tables with the name delete in them.  

      SET ANSI_NULLS ON
      GO
      SET QUOTED_IDENTIFIER ON
      GO
      -- =============================================
      -- Author:              
      -- Create date: 
      -- Description: 
      -- =============================================
      alter PROCEDURE free_space

      AS
      BEGIN
              -- SET NOCOUNT ON added to prevent extra result sets from
              -- interfering with SELECT statements.
              SET NOCOUNT ON;

              DECLARE cursor_value CURSOR
              FOR
                              select name from sys.tables where name like '%delete%'
              FOR READ ONLY

              DECLARE @cursor_row_value varchar(120)
              DECLARE @S nvarchar(500);
              OPEN cursor_value
              FETCH cursor_value INTO @cursor_row_value
              WHILE (@@FETCH_STATUS=0) BEGIN
                         set @S = 'drop table ['+@cursor_row_value+']'
                         execute sp_executesql @S
                         FETCH cursor_value INTO @cursor_row_value
              END
              CLOSE cursor_value
              DEALLOCATE cursor_value

     END
     GO




TIP 25:

  What is the difference between UNION and UNION ALL?  UNION only returns the distinct
  number of rows and is slower than UNION ALL.




TIP 26:

  What is the difference between NVARCHAR, VARCHAR and CHAR?
  NVARCHAR takes two bytes, VARCHAR takes 1 byte, and CHAR is fixed
  length.


TIP 27:

  Order can make a difference when doing updates

   
     IF object_id('tempdb..#t1') IS NOT NULL
     BEGIN
        DROP TABLE #t1
     END
     IF object_id('tempdb..#t2') IS NOT NULL
     BEGIN
        DROP TABLE #t2
     END
     IF object_id('tempdb..#t3') IS NOT NULL
     BEGIN
        DROP TABLE #t3
     END
   
   
   
     create table #t1 (a int, b int);
     insert into #t1 (a,b) values (1,2);
     insert into #t1 (a,b) values (1,5);
   
     create table #t2 (a int ,b int, c int, d int, [order] int);
     insert into #t2 (a,b,c,d,[order]) values (1,2,10,10,0);
     insert into #t2 (a,b,c,d,[order]) values (1,2,50,60,1);

     -- Change order here:
     --select * into #t3 from #t2 order by [order] desc
     select * into #t3 from #t2 order by [order] asc
   
     update #t1
     set #t1.a=#t3.c, #t1.b=#t3.d
     from #t1 ,#t3
     where #t1.a=#t3.a and #t1.b=#t3.b
   
   
     select * from #t1
   
   

TIP 28:

  Need a listing of all stored procedures on the system that you
  created?


     SELECT * -- Stored Procs
     FROM sys.objects
     WHERE objectproperty( object_id, N'IsMSShipped' ) = 0
     AND objectproperty( object_id, N'IsProcedure' ) = 1

  Below are some other common methods. But take a look at the
  following below and note the condition where N'IsMSShipped' is
  false, or equal to zero.

     SELECT * -- Functions
     FROM sys.objects
     WHERE objectproperty( object_id, N'IsMSShipped' ) = 0
     AND ( objectproperty( object_id, N'IsTableFunction' ) = 1
     OR objectproperty( object_id, N'IsScalarFunction' ) = 1 )

     SELECT * -- User tables
     FROM sys.objects
     WHERE objectproperty( object_id, N'IsMSShipped' ) = 0
     AND objectproperty( object_id, N'IsTable' ) = 1

     SELECT * -- Views
     FROM sys.objects
     WHERE objectproperty( object_id, N'IsMSShipped' ) = 0
     AND objectproperty( object_id, N'IsView' ) = 1


   Reference:
       http://msdn2.microsoft.com/en-us/library/ms176105.aspx
       http://sqlblogcasts.com/blogs/tonyrogerson/archive/2006/06/29/850.aspx



TIP 29:

  Combining two tables into 1 pivot table

   
     create table p1 (a int, b int)
     create table p2 (a int, b int)
   
   
     delete from p1;
     delete from p2;
     insert into p1 (a,b) values (1,34);
     insert into p1 (a,b) values (2,12);
     insert into p1 (a,b) values (3,22);
   
     insert into p2 (a,b) values (1,500);
     insert into p2 (a,b) values (2,512);
     insert into p2 (a,b) values (4,600);
   
   
   
     select g.a,g.b,g.c
     from
     (
      select p1.a, p1.b,p2.b as c from p1, p2
      where p1.a=p2.a
     union all
           select p1.a,p1.b,null as c from p1 left outer join
            p2 on p1.a=p2.a
            where p2.a is null
     union all
           select p2.a,null as b,p2.b as c from p2 left outer join
            p1 on p2.a=p1.a
            where p1.a is null
     ) as g
   
     /* Results:
     a  b c

     1  34 500
     2  12 512
     3  22 NULL
     4  NULL 600

     */