چند كوئري براي گرفتن ليست ستون ها به همراه جزئيات (بدون استفاده از sys.objects)

[bahman_akbarzadeh]

سلام
يه مدت دنبال اين بودم كه چطور ميشه، ليست ديتابيس ها، جدول ها و ستون ها رو از Sql Server گرفت.
اما هر چي گشتم، همه از ديد sysobject استفاده ميكردن كه زياد كارايي نداشت. (البته سايت هاي خارجي)
براي همين نشستم داخل ديد هاي سيستمي رو گشتم و اين كوئري هارو تونستم بنويسم :

ليست ديتابيس ها



Select name From sys.databases

ليست جداول (بايد قبلا به ديتابيس كانكت شود)


Select name From sys.tables Where name <> 'sysdiagrams'
ليست ستون ها در تمام جداول


SELECT c.COLUMN_NAME, c.IS_NULLABLE, c.DATA_TYPE, c.CHARACTER_MAXIMUM_LENGTH, LEFT(cc.CONSTRAINT_NAME, 2) AS KEY_STATE, ic.is_identity FROM INFORMATION_SCHEMA.COLUMNS AS c LEFT OUTER JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE AS cc ON c.TABLE_NAME = cc.TABLE_NAME AND c.COLUMN_NAME = cc.COLUMN_NAME LEFT OUTER JOIN sys.identity_columns AS ic ON c.COLUMN_NAME = ic.name
ليست ستونها با دو ديد ديگه جوين شده تا كليد اصلي يا خارجي بودن و Auto Increment بودنش هم مشخص بشه.

در نهايت تنها چيزي كه كم داره خاصيت Description ستون ها هستش و اگه از دوستان، كسي اينو ميدونه يا ميتونه پيداش كنه، به ما هم اطلاع بده كه خيلي لازم دارم.

[bahman_akbarzadeh]

كسي نمي دونه خاصيت Description ستونها رو از كجا گير بيارم؟

[SabaSabouhi]

با سلام
دوست گرامی، چرا اصرار دارید از sysobject و syscolumns استفاده نکنید؟

صبا صبوحی

[bahman_akbarzadeh]

چون اطلاعات كاملي رو نمي شه ازش در آورد.
مخصوصا syscolumns كه فقط كليد خارجي داره و اگه از همون ها هم استفاده كنم و به منبعش برسم، بازم كليد اصلي و كليد خارجي و چند تا چيز ديگه، مشخص نمي شه.

[SabaSabouhi]

چون اطلاعات كاملي رو نمي شه ازش در آورد.
مخصوصا syscolumns كه فقط كليد خارجي داره و اگه از همون ها هم استفاده كنم و به منبعش برسم، بازم كليد اصلي و كليد خارجي و چند تا چيز ديگه، مشخص نمي شه.

سلام
شما اون چند تا چیزی که لازم داری رو بگو، شاید من بتونم راهنمایی کنم.
برای کلید اصلی از جدول sysobject استفاده کن با شرط این که xtype برابر PK باشه و parent_obj برابر id مربوط به جدول.

صبا صبوحی

[bahman_akbarzadeh]

من اول از همه، sysobject رو نگاه كردم.
درسته كه نوع هر آبجكت رو مشخص ميكنه، ولي به همين راحتي نمي تونه بگه كه از كدوم جدوله.

اگر شما از sysobject بتونين كوئري كه من در بالا براي ليست ستون ها گرفتم، به انضمام خاصيت Description هر ستون رو بگيريد، ممنونتون ميشم.

[Soroush.Sarabi]

خب دوست عزیز چرا از Schema استفاده نمیکنی :

Select * From Information_Schema.Tables

Select * From Information_Schema.Columns

,...

-----------------------------------------
وب سایت رسمی سروش سارابی
http://www.soroush-sarabi.com

وبلاگ تخصصی برنامه نویسی
http://soroush-sarabi.blogspot.com

[bahman_akbarzadeh]

خب دوست عزیز چرا از Schema استفاده نمیکنی :

Select * From Information_Schema.Tables

Select * From Information_Schema.Columns


منم كه دارم از همين ها استفاده ميكنم.
در كوئري آخر ، INFORMATION_SCHEMA.COLUMNS و INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE و sys.identity_columns رو براي گرفتن اطلاعات ستون ها با هم Join كردم.
فقط Description پيدا نشد !!

[Soroush.Sarabi]

منم كه دارم از همين ها استفاده ميكنم.
در كوئري آخر ، INFORMATION_SCHEMA.COLUMNS و INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE و sys.identity_columns رو براي گرفتن اطلاعات ستون ها با هم Join كردم.
فقط Description پيدا نشد !!

:قهقهه: کد اسکرول داشت آخرش رو ندیدم.

اما برای Description در Sql 2005 یک مجموعه داریم به نام Property که Description هم یکی

از اون هاست که هم نام دارند هم مقدار و برای دسترسی به اونها می تونی از

sys.extended_properties

استفاده کنی.

اینم نمونه کد اگه خواستی



SELECT
[Table Name] =OBJECT_NAME(c.object_id),
[Column Name] = c.name,
[Description] = ex.value
FROM
sys.columns c
LEFTOUTERJOIN
sys.extended_properties ex
ON
ex.major_id = c.object_id
AND ex.minor_id = c.column_id
AND ex.name ='MS_Description'
WHERE
OBJECTPROPERTY(c.object_id,'IsMsShipped')=0
-- AND OBJECT_NAME(c.object_id) = 'your_table'
ORDER
BYOBJECT_NAME(c.object_id), c.column_id

[bahman_akbarzadeh]

خيلي ممنون
بالاخره پيدا شد و مشكلم حل شد.
ولي بازم مجبور شدم براي Join كردنش، از syscolumns استفاده كنم.
اينم كوئري نهايي براي ليست ستون ها به همراه جزئيات و البته Description :



SELECT DISTINCT c.TABLE_NAME, c.COLUMN_NAME, c.IS_NULLABLE, c.DATA_TYPE, c.CHARACTER_MAXIMUM_LENGTH, ep.value AS Description,
LEFT(cc.CONSTRAINT_NAME, 2) AS KEY_STATE, ic.is_identity
FROM INFORMATION_SCHEMA.COLUMNS AS c LEFT OUTER JOIN
INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE AS cc ON c.TABLE_NAME = cc.TABLE_NAME AND
c.COLUMN_NAME = cc.COLUMN_NAME LEFT OUTER JOIN
sys.identity_columns AS ic ON c.COLUMN_NAME = ic.name LEFT OUTER JOIN
sys.syscolumns AS sc ON c.COLUMN_NAME = sc.name LEFT OUTER JOIN
sys.extended_properties AS ep ON sc.id = ep.major_id AND sc.colid = ep.minor_id
WHERE (c.TABLE_NAME <> 'sysdiagrams') AND (c.TABLE_NAME <> 'dtproperties')


فقط نام ستون ها نبايد با نام ستون هاي جداول سيستمي يكي باشه، چون يه سري ركورد اضافي بر ميگردونه.