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’;
 


Advertisements

February 7, 2007 - Posted by | MSSQL

No comments yet.

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: