MS SQL Server Property

  1. SERVERPROPERTY (Transact-SQL)
  2. DATABASEPROPERTYEX (Transact-SQL)
  3. List views
  4. List tables

1.SERVERPROPERTY (Transact-SQL)

Returns property information about the server instance.

ตรวจสอบ Collation ของ SQL Server เช่น SQL_Latin1_General_CP1_CI_AS

SELECT SERVERPROPERTY('Collation')

ตรวจสอบ Edition ของ SQL Server เช่น

  • Enterprise Edition
  • Developer Edition
  • Express Edition
  • Standard Edition
  • Web Edition
  • SQL Azure’
SELECT SERVERPROPERTY('Edition')

2.DATABASEPROPERTYEX (Transact-SQL)

For a specified database in SQL Server, this function returns the current setting of the specified database option or property.

DATABASEPROPERTYEX ( database , property )

ตรวจสอบ Collation ของดาต้าเบส MyDB เช่น Thai_CI_AS

SELECT DATABASEPROPERTYEX('MyDB', 'Collation') SQLCollation;

3.List views

SELECT 
	OBJECT_SCHEMA_NAME(o.object_id) schema_name,
	o.name
FROM
	sys.objects as o
WHERE
	o.type = 'V';

หรือ

select schema_name(schema_id) as schema_name,
       name as view_name
from sys.views
order by schema_name,
         view_name;

หรือ

SELECT 
  TABLE_SCHEMA,
  TABLE_NAME
FROM INFORMATION_SCHEMA.VIEWS;

4.List tables

SELECT 
	OBJECT_SCHEMA_NAME(o.object_id) schema_name,
	o.name
FROM
	sys.objects as o
WHERE
	o.type = 'U';

ดู object type ต่าง

select type, type_desc
FROM sys.objects as o
group by type, type_desc
order by type
type	type_desc
F 	FOREIGN_KEY_CONSTRAINT
IT	INTERNAL_TABLE
P 	SQL_STORED_PROCEDURE
PK	PRIMARY_KEY_CONSTRAINT
S 	SYSTEM_TABLE
U 	USER_TABLE
UQ	UNIQUE_CONSTRAINT
V 	VIEW
...

หรือ ดูตารางจาก INFORMATION_SCHEMA

SELECT 
  TABLE_SCHEMA,
  TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'