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'

Other

ดูเวอร์ชันของ SQL Server

SELECT @@VERSION AS 'SQL Server Version';  

ดู Dependencies ของตาราง

To view the objects that depend on a table

USE AdventureWorks2012;  
GO  
SELECT * FROM sys.sql_expression_dependencies  
WHERE referencing_id = OBJECT_ID(N'Production.vProductAndDescription');   
GO  

To view the dependencies of a table

USE AdventureWorks2012;   
GO  
SELECT * FROM sys.sql_expression_dependencies  
WHERE referenced_id = OBJECT_ID(N'Production.Product');   
GO  

ชื่อตารางอยู่ที่ฟิลด์ sys.sql_expression_dependencies.referenced_entity_name