Tag Archive object

DECLARE @search VARCHAR(100) = '%อัตราแลกเปลี่ยน%';

SELECT
	DB_NAME() AS DatabaseName
	, SCHEMA_NAME(t.schema_id) AS SchemaName
	, t.name AS TableName
	, QUOTENAME(DB_NAME()) + '.' + QUOTENAME(SCHEMA_NAME(t.schema_id)) + '.' + QUOTENAME(t.name) AS FullyQualifiedTableName
	, c.name AS ColumnName
	, ty.name AS ColumnType
	, CASE
		WHEN ty.name IN (
			'char', 'varchar', 'nchar', 'nvarchar', 'binary', 'varbinary'
		) THEN CAST(c.max_length AS VARCHAR(10))
		WHEN ty.name IN (
			'decimal', 'numeric'
		) THEN CAST(c.precision AS VARCHAR(10)) + ',' + CAST(c.scale AS VARCHAR(10))
		ELSE ''
	END AS ColumnSize
	, CAST(COALESCE(ep_t.value, ep_c.value) AS NVARCHAR(MAX)) AS Description
	, CASE
		WHEN t.name LIKE @search
		OR CAST(ep_t.value AS NVARCHAR(MAX)) LIKE @search THEN 'Table'
		WHEN c.name LIKE @search
		OR CAST(ep_c.value AS NVARCHAR(MAX)) LIKE @search THEN 'Column'
		ELSE 'Unknown'
	END AS FoundIn
FROM
	sys.tables t
LEFT JOIN
    sys.columns c ON
	t.object_id = c.object_id
LEFT JOIN
    sys.types ty ON
	c.system_type_id = ty.system_type_id
	AND c.user_type_id = ty.user_type_id
LEFT JOIN
    sys.extended_properties ep_t
    ON
	t.object_id = ep_t.major_id
	AND ep_t.minor_id = 0
	AND ep_t.name = 'MS_Description'
LEFT JOIN
    sys.extended_properties ep_c
    ON
	t.object_id = ep_c.major_id
	AND c.column_id = ep_c.minor_id
	AND ep_c.name = 'MS_Description'
WHERE
	t.name LIKE @search
	OR CAST(ep_t.value AS NVARCHAR(MAX)) LIKE @search
	OR c.name LIKE @search
	OR CAST(ep_c.value AS NVARCHAR(MAX)) LIKE @search
ORDER BY
	TableName
	, ColumnName;