SQL Server 2008 internals notes (CHAPTER ONE)

1. select serverproperty( 'Edition') ------------ e.g. Enterprise Edition ( or others)

select serverproperty('EngineEdition') ---------- e.g. 3 ( means edtion is either Enterprise , enterprise Evaluation or Developer. These three have exactly the same features and functionality.

2. Exec SP_help ( to view all system base tables with a system administrator privilege)

3. execute sys.objects to see the names of all system tables

use master ;

select name from sys.objects where type_desc = 'System_table' ;

4. Catalog Views in SQL Server 2008

( all are in sys schema)

sys.bojects , sys.database , sys.indexes , sys.databases

5. View the description of a table ( columns)

to use stored procedure (system procedures) sp_columns

e.g. exec sp_columns @table_name=N'tablename' , @table_owner=N'tableschema' ;

6. We can use function object_definition(object_id( 'some_object') or system procedure sp_helptext 'some_object' to view the definitions of views , procedures , triggers and user-defined functions(UDFs)

e.g. select object_definition(object_id('some_object')) ; --- unavailable for table definitions

exec sp_helptext 'some_object'

7. View the schema of a database in one of instances in SQL 2008

checking Information_schema

e.g. select table_name from information_schema.tables ; ( all tables and views returned)

select table_name from information_schema.tables where table_type='Base Table'; ( only tables returned)

8. View Server Version -> select @@version ;

In ADO.net , Sqlconnection cn = ... ; cn.ServerVersion;(property)