Deep Tech Fusion

tech related weblog

Mssql 200 vs 2005

Comparison between SQL Server 2005 & 2000 .

Feature 2005 2000
Show all identity columns SELECT object_name(object_id),name
FROM sys.identity_columns;

last value of the identity column
SELECT name,last_value
FROM sys.identity_columns;		 SELECT object_name(id) AS table_name, name AS column_name
FROM syscolumns
WHERE columnproperty(id,name,’IsIdentity’)=1;
Show all database names SELECT name
FROM sys.databases		 SELECT name
FROM master..sysdatabases
Show all tables names SELECT name
FROM sys.tables;

replicated table
SELECT *
FROM sys.tables
WHERE is_replicated =1;		 SELECT name
FROM sysobjects
WHERE type=’U';
Show all Procedures names SELECT name
FROM sys.procedures;

startup procedure
startup procedure or used in replicationSELECT name
FROM sys.procedures
WHERE is_auto_executed=0;used in replicationSELECT name
FROM sys.procedures
WHERE execution_replicated=1;		 SELECT name
FROM sysobjects WHERE type=’P’
Show all views SELECT name
FROM sys.views;

replicated view
SELECT *
FROM sys.views
WHERE is_replicated =1;		 SELECT name
FROM sysobjects
WHERE type=’V';
Show all Triggers names SELECT name
FROM sys.triggers
WHERE parent_class=1;

AFTER trigger or INSTEAD of trigger
SELECT name
FROM sys.triggers
WHERE type=’TR’;SELECT name
FROM sys.triggers
WHERE is_instead_of_trigger=1;		 SELECT name
FROM sysobjects
WHERE type=’TR’;
Show all data types SELECT *
FROM sys.systypes;		 SELECT *
FROM systypes;
Show all logins SELECT *
FROM sys.sql_logins;		 SELECT *
FROM master..syslogins
WHERE isntgroup=0 and isntname=0;
Show all dependencies of the Objects SELECT *
FROM sys.sql_dependencies;		 SELECT *
FROM sysdepends;
Show all error messages SELECT *
FROM sys.messages;		 SELECT *
FROM master..sysmessages;
Show all database files SELECT name, physical_name
FROM sys.database_files;		 SELECT name, filename
FROM sysfiles;
Show all computed columns SELECT name
FROM sys.computed_columns
WHERE object_id =object_id(‘table_name’); 		SELECT name
FROM syscolumns
WHERE id =object_id(‘TableName’)
AND iscomputed=1;
Show all type of index SELECT object_name(object_id),name, type_desc
FROM sys.indexes
WHERE type_desc =’CLUSTERED’;

&&
SELECT object_name(object_id),name, type_desc
FROM sys.indexes
WHERE type_desc =’HEAP’;

&&
SELECT object_name(object_id),name, type_desc
FROM sys.indexes
WHERE type_desc =’NONCLUSTERED’;

&&
SELECT object_name(object_id),name, type_desc
FROM sys.indexes
WHERE type_desc =’XML’;		   






                


                February 7, 2007 

								
                  Posted by               	   |
                  MSSQL				   |
                  Leave a Comment                  								

             	


             


              

          
              


                
Transact-SQL – Operators


                
    

  1. + (Add):
    
    Adds two numbers. This addition arithmetic operator can also add a number, in days, to a date.
    

    SELECT c.FirstName, c.LastName, VacationHours, SickLeaveHours,
    VacationHours + SickLeaveHours AS 'Total Hours Away'
FROM HumanResources.Employee AS e
    JOIN Person.Contact AS c ON e.ContactID = c.ContactID
    

    2. 


  2. 

    3. 



                


                December 28, 2006 

								
                  Posted by               	   |
                  MSSQL				   |
                  Leave a Comment                  								

             	


             


              

          

        
		


			
     


		


      


      



      



    


  










	
	

	

			
	

		Follow
		

			
					

		
			

			
Get every new post delivered to your Inbox.