Code
alter procedure [dbo].SelectX
(
@tableName nvarchar(50),
@FieldName nvarchar(50) = null
)
as
begin
/*
By hebkkun@163.com
2009-6-2
查询表信息存储过程
*/
SELECT
CAST(clmns.colid AS int) AS FieldIndex,
clmns.[name] AS Name,
usrt.[name] AS FieldType,
clmns.length AS FieldLen,
CASE CAST(clmns.isnullable AS bit) WHEN 1 THEN 'TRUE' ELSE 'FALSE' END AS FieldNullable,
defaults.text AS FieldColumnDef,
CASE CAST(COLUMNPROPERTY(clmns.id, clmns.[name], N'IsIdentity') AS int) WHEN 1 THEN 'TRUE' ELSE 'FALSE' END AS IsPrimaryKey,
sys.extended_properties.[value] AS FieldRemark
FROM
dbo.sysobjects AS tbl
LEFT JOIN dbo.syscolumns AS clmns ON clmns.id=tbl.id
LEFT JOIN dbo.systypes AS usrt ON usrt.xusertype = clmns.xusertype
LEFT JOIN dbo.syscomments AS defaults ON defaults.id = clmns.cdefault
LEFT JOIN sys.extended_properties ON
( sys.extended_properties.minor_id = clmns.colid
AND sys.extended_properties.major_id = clmns.id)
WHERE 1 = 1
AND (tbl.[type] = 'U' OR tbl.[type] = 'S')
AND tbl.[name] = @tableName
AND (nullif(@FieldName,'') is null or clmns.[name] like '%' + @FieldName + '%')
ORDER BY
FieldIndex ASC
end
alter procedure [dbo].SelectX
(
@tableName nvarchar(50),
@FieldName nvarchar(50) = null
)
as
begin
/*
By hebkkun@163.com
2009-6-2
查询表信息存储过程
*/
SELECT
CAST(clmns.colid AS int) AS FieldIndex,
clmns.[name] AS Name,
usrt.[name] AS FieldType,
clmns.length AS FieldLen,
CASE CAST(clmns.isnullable AS bit) WHEN 1 THEN 'TRUE' ELSE 'FALSE' END AS FieldNullable,
defaults.text AS FieldColumnDef,
CASE CAST(COLUMNPROPERTY(clmns.id, clmns.[name], N'IsIdentity') AS int) WHEN 1 THEN 'TRUE' ELSE 'FALSE' END AS IsPrimaryKey,
sys.extended_properties.[value] AS FieldRemark
FROM
dbo.sysobjects AS tbl
LEFT JOIN dbo.syscolumns AS clmns ON clmns.id=tbl.id
LEFT JOIN dbo.systypes AS usrt ON usrt.xusertype = clmns.xusertype
LEFT JOIN dbo.syscomments AS defaults ON defaults.id = clmns.cdefault
LEFT JOIN sys.extended_properties ON
( sys.extended_properties.minor_id = clmns.colid
AND sys.extended_properties.major_id = clmns.id)
WHERE 1 = 1
AND (tbl.[type] = 'U' OR tbl.[type] = 'S')
AND tbl.[name] = @tableName
AND (nullif(@FieldName,'') is null or clmns.[name] like '%' + @FieldName + '%')
ORDER BY
FieldIndex ASC
end
使用的时候
exec SelectX @tableName=N'表名',@FieldName=N'字段名'
3
Name
nvarchar
100
FALSE
('')
FALSE
名称
4
EnName
nvarchar
100
FALSE
('')
FALSE
英文名称
5
CnName
nvarchar
100
FALSE
('')
FALSE
中文名称
exec gettable N'表名',N'字段名'
输入字段名则模糊查询,否则查询所有列
1
ID
int
4
FALSE
NULL
TRUE
ID
2
DataServerID
int
4
FALSE
((0))
FALSE
服务器ID
3
Name
nvarchar
100
FALSE
('')
FALSE
名称
4
EnName
nvarchar
100
FALSE
('')
FALSE
英文名称
5
CnName
nvarchar
100
FALSE
('')
FALSE
中文名称
6
ExGUID
nvarchar
200
FALSE
('')
FALSE
业务标识键
7
ExInclude
bit
1
FALSE
((1))
FALSE
参与生成
8
ExReamrk
nvarchar
400
FALSE
('')
FALSE
备注
9
Ext
nvarchar
400
FALSE
('')
FALSE
扩展
------------------------------------------------------