---

想要在新接觸的資料庫中尋找想要的欄位名稱
可是又懶得去翻厚厚的文件,用這個懶人語法就對啦!


SQL QUERY===========
SELECT         dbo.sysobjects.name AS table_name, dbo.syscolumns.name AS column_name,
               dbo.systypes.name AS datatype, dbo.syscolumns.length AS length
FROM           dbo.sysobjects INNER JOIN
               dbo.syscolumns ON dbo.sysobjects.id = dbo.syscolumns.id INNER JOIN
               dbo.systypes ON dbo.syscolumns.xtype = dbo.systypes.xtype
WHERE          dbo.sysobjects.xtype = 'U'
ORDER BY       dbo.sysobjects.name, dbo.syscolumns.colid

SP==================
CREATE PROCEDURE sp_get_column_names
@c_name varchar(128) = ''

AS

SET NOCOUNT ON

BEGIN
IF @c_name <> ''
SELECT         dbo.sysobjects.name AS table_name, dbo.syscolumns.name AS column_name,
               dbo.systypes.name AS datatype, dbo.syscolumns.length AS length
FROM           dbo.sysobjects INNER JOIN
               dbo.syscolumns ON dbo.sysobjects.id = dbo.syscolumns.id INNER JOIN
               dbo.systypes ON dbo.syscolumns.xtype = dbo.systypes.xtype
WHERE          dbo.sysobjects.xtype = 'U' AND dbo.syscolumns.name = @c_name
ORDER BY       dbo.sysobjects.name, dbo.syscolumns.colid
ELSE
SELECT         dbo.sysobjects.name AS table_name, dbo.syscolumns.name AS column_name,
               dbo.systypes.name AS datatype, dbo.syscolumns.length AS length
FROM           dbo.sysobjects INNER JOIN
               dbo.syscolumns ON dbo.sysobjects.id = dbo.syscolumns.id INNER JOIN
               dbo.systypes ON dbo.syscolumns.xtype = dbo.systypes.xtype
WHERE          dbo.sysobjects.xtype = 'U'
ORDER BY       dbo.sysobjects.name, dbo.syscolumns.colid
END

SET NOCOUNT OFF
GO

--
※ 來源: DISP BBS (http://disp.cc)
※ 作者: Farland  來自: 122.116.69.26  時間: 2010-04-02 15:00:34
※ 編輯: Farland  來自: 122.116.69.26  時間: 2010-04-02 15:05:33