kerryyu
超级版主
帖子
155
精华
0
无忧币 1172
积分 492
阅读权限 200
来自 (保密)
|
发表于:2008-3-26 11:01
标题：2000用的一个select 语句有点错误,帮忙改一下
＜上一帖 |
下一帖＞
SELECT
'N/A' AS NAMES_Layer,
0 AS IS_IDENTITY,
'N/A' AS SEED_VALUE,
'N/A' AS INCREMENT_VALUE,
'N/A' AS COLUMN_NAME,
'N/A' AS DEFAULT_VALUE,
'T' AS NAME_KIND,
name AS NAME_ID,
'N/A' AS CONSTRAINT_TYPE,
0 AS ORDINAL_POSITION,
'N/A' AS COLUMN_DEFAULT,
'N/A' AS IS_NULLABLE,
'N/A' AS DATA_TYPE,
0 AS CHARACTER_MAXIMUM_LENGTH,
0 AS CHARACTER_OCTET_LENGTH,
0 AS NUMERIC_PRECISION,
0 AS NUMERIC_PRECISION_RADIX,
0 AS NUMERIC_SCALE,
0 AS DATETIME_PRECISION,
cast('N/A' as nvarchar(4000)) AS COLLATION_NAME,
'N/A' AS EXTEND_VALUE,
crdate AS CREATE_DATA,
refdate AS ALTER_DATA
FROM
SYSOBJECTS
WHERE
xtype = 'U'
UNION all
--视图
SELECT
VIEW_DEFINITION AS NAMES_Layer,
0 AS IS_IDENTITY,
'N/A' AS SEED_VALUE,
'N/A' AS INCREMENT_VALUE,
'N/A' AS COLUMN_NAME,
'N/A' AS DEFAULT_VALUE,
'V' AS NAME_KIND,
name AS NAME_ID,
'N/A' AS CONSTRAINT_TYPE,
0 AS ORDINAL_POSITION,
'N/A' AS COLUMN_DEFAULT,
'N/A' AS IS_NULLABLE,
'N/A' AS DATA_TYPE,
0 AS CHARACTER_MAXIMUM_LENGTH,
0 AS CHARACTER_OCTET_LENGTH,
0 AS NUMERIC_PRECISION,
0 AS NUMERIC_PRECISION_RADIX,
0 AS NUMERIC_SCALE,
0 AS DATETIME_PRECISION,
cast('N/A' as nvarchar(4000)) AS COLLATION_NAME,
'N/A' AS EXTEND_VALUE,
crdate AS CREATE_DATA,
refdate AS ALTER_DATA
FROM
SYSOBJECTS,INFORMATION_SCHEMA.VIEWS
WHERE
xtype = 'V' AND name = TABLE_NAME
UNION all
--字段及详细类型
SELECT
(SELECT name FROM sysobjects WHERE ID = c.id) AS NAMES_Layer,
0 AS IS_IDENTITY,
CONVERT(VARCHAR,ident_seed(tb.name)) AS SEED_VALUE,
CONVERT(VARCHAR,ident_incr(tb.name)) AS INCREMENT_VALUE,
'N/A' AS COLUMN_NAME,
'N/A' AS DEFAULT_VALUE,
'F' AS NAME_KIND,
c.name AS NAME_ID,
'N/A' AS CONSTRAINT_TYPE,
c.colid AS ORDINAL_POSITION,
CAST(p.[value] AS varchar(8000)) AS COLUMN_DEFAULT,
'N/A' AS IS_NULLABLE,
t.name AS DATA_TYPE,
c.length AS CHARACTER_MAXIMUM_LENGTH,
c.length AS CHARACTER_OCTET_LENGTH,
c.prec AS NUMERIC_PRECISION,
0 AS NUMERIC_PRECISION_RADIX,
c.scale AS NUMERIC_SCALE,
0 AS DATETIME_PRECISION,
cast(c.collation as nvarchar(4000)) AS COLLATION_NAME,
'' AS EXTEND_VALUE,
getdate() AS CREATE_DATA,
getdate() AS ALTER_DATA
FROM
syscolumns c
INNER JOIN systypes t ON c.xusertype = t.xusertype
--INNER JOIN systypes st ON c.xtype = st.xusertype
--LEFT OUTER JOIN sysobjects d ON d.id = c.cdefault
--LEFT OUTER JOIN sysusers du ON du.uid = d.uid
--LEFT OUTER JOIN syscomments cm ON c.cdefault = cm.id
--LEFT OUTER JOIN syscomments fr ON fr.id = c.id AND fr.number = c.colid
--LEFT OUTER JOIN sysobjects r ON r.id = c.domain
--LEFT OUTER JOIN sysusers ru ON ru.uid = r.uid
INNER JOIN sysobjects tb ON tb.id = c.id
INNER JOIN sysusers u ON u.uid = tb.uid
LEFT OUTER JOIN sysproperties p ON p.id = c.id AND p.smallid = c.colid AND p.type = 4 AND p.name = 'MS_Description'
WHERE
u.name = N'dbo'
UNION all
--主键
SELECT
(select name from sysobjects where id = a.parent_obj) AS NAMES_Layer,
0 AS IS_IDENTITY,
'N/A' AS SEED_VALUE,
'N/A' AS INCREMENT_VALUE,
(SELECT top 1 c.name
FROM
sysindexkeys k
INNER JOIN sysindexes i ON k.id = i.id and k.indid = i.indid
INNER JOIN syscolumns c ON k.id = c.id AND k.colid = c.colid
where i.name = a.name) AS COLUMN_NAME,
'N/A' AS DEFAULT_VALUE,
'K' AS NAME_KIND,
a.name AS NAME_ID,
'PRIMARY KEY' AS CONSTRAINT_TYPE,
0 AS ORDINAL_POSITION,
'N/A' AS COLUMN_DEFAULT,
'N/A' AS IS_NULLABLE,
'N/A' AS DATA_TYPE,
0 AS CHARACTER_MAXIMUM_LENGTH,
0 AS CHARACTER_OCTET_LENGTH,
0 AS NUMERIC_PRECISION,
0 AS NUMERIC_PRECISION_RADIX,
0 AS NUMERIC_SCALE,
0 AS DATETIME_PRECISION,
'N/A' AS COLLATION_NAME,
'N/A' AS EXTEND_VALUE,
crdate AS CREATE_DATA,
GETDATE() AS ALTER_DATA
FROM
sysobjects as a
WHERE
xtype = 'PK'
UNION ALL
--唯一键
SELECT
(select name from sysobjects where id = a.parent_obj) AS NAMES_Layer,
0 AS IS_IDENTITY,
'N/A' AS SEED_VALUE,
'N/A' AS INCREMENT_VALUE,
(SELECT top 1 c.name
FROM
sysindexkeys k
INNER JOIN sysindexes i ON k.id = i.id and k.indid = i.indid
INNER JOIN syscolumns c ON k.id = c.id AND k.colid = c.colid
where i.name = a.name) AS COLUMN_NAME,
'N/A' AS DEFAULT_VALUE,
'K' AS NAME_KIND,
a.name AS NAME_ID,
'UNIQUE' AS CONSTRAINT_TYPE,
0 AS ORDINAL_POSITION,
'N/A' AS COLUMN_DEFAULT,
'N/A' AS IS_NULLABLE,
'N/A' AS DATA_TYPE,
0 AS CHARACTER_MAXIMUM_LENGTH,
0 AS CHARACTER_OCTET_LENGTH,
0 AS NUMERIC_PRECISION,
0 AS NUMERIC_PRECISION_RADIX,
0 AS NUMERIC_SCALE,
0 AS DATETIME_PRECISION,
'N/A' AS COLLATION_NAME,
'N/A' AS EXTEND_VALUE,
crdate AS CREATE_DATA,
GETDATE() AS ALTER_DATA
FROM
sysobjects as a
WHERE
xtype = 'UQ'
UNION ALL
--check约束
SELECT
(select name from sysobjects where id = a.parent_obj) AS NAMES_Layer,
0 AS IS_IDENTITY,
'N/A' AS SEED_VALUE,
'N/A' AS INCREMENT_VALUE,
'N/A' AS COLUMN_NAME,
(SELECT TEXT FROM syscomments WHERE ID = a.id) AS DEFAULT_VALUE,
'K' AS NAME_KIND,
a.name AS NAME_ID,
'CHECK' AS CONSTRAINT_TYPE,
0 AS ORDINAL_POSITION,
'N/A' AS COLUMN_DEFAULT,
'N/A' AS IS_NULLABLE,
'N/A' AS DATA_TYPE,
0 AS CHARACTER_MAXIMUM_LENGTH,
0 AS CHARACTER_OCTET_LENGTH,
0 AS NUMERIC_PRECISION,
0 AS NUMERIC_PRECISION_RADIX,
0 AS NUMERIC_SCALE,
0 AS DATETIME_PRECISION,
'N/A' AS COLLATION_NAME,
'N/A' AS EXTEND_VALUE,
crdate AS CREATE_DATA,
GETDATE() AS ALTER_DATA
FROM
sysobjects as a
WHERE
xtype = 'C'
UNION ALL
SELECT
(select name from sysobjects where id = a.parent_obj) AS NAMES_Layer,
0 AS IS_IDENTITY,
'N/A' AS SEED_VALUE,
'N/A' AS INCREMENT_VALUE,
( SELECT c.name
FROM
syscolumns c
LEFT OUTER JOIN sysobjects d ON d.id = c.cdefault
LEFT OUTER JOIN syscomments cm ON c.cdefault = cm.id
WHERE d.parent_obj = a.parent_obj and d.name = a.name)
AS COLUMN_NAME,
( SELECT cm.text
FROM
syscolumns c
LEFT OUTER JOIN sysobjects d ON d.id = c.cdefault
LEFT OUTER JOIN syscomments cm ON c.cdefault = cm.id
WHERE d.parent_obj = a.parent_obj and d.name = a.name)
AS DEFAULT_VALUE,
'D' AS NAME_KIND,
a.name AS NAME_ID,
'DEFAULT' AS CONSTRAINT_TYPE,
0 AS ORDINAL_POSITION,
'N/A' AS COLUMN_DEFAULT,
'N/A' AS IS_NULLABLE,
'N/A' AS DATA_TYPE,
0 AS CHARACTER_MAXIMUM_LENGTH,
0 AS CHARACTER_OCTET_LENGTH,
0 AS NUMERIC_PRECISION,
0 AS NUMERIC_PRECISION_RADIX,
0 AS NUMERIC_SCALE,
0 AS DATETIME_PRECISION,
'N/A' AS COLLATION_NAME,
'N/A' AS EXTEND_VALUE,
crdate AS CREATE_DATA,
GETDATE() AS ALTER_DATA
FROM
sysobjects as a
WHERE
xtype = 'D'
UNION ALL
--存储过程
SELECT
'N/A' AS NAMES_Layer,
0 AS IS_IDENTITY,
'N/A' AS SEED_VALUE,
'N/A' AS INCREMENT_VALUE,
'N/A' AS COLUMN_NAME,
'N/A' AS TEXT,
'P' AS NAME_KIND,
SPECIFIC_NAME AS NAME_ID,
'N/A' AS CONSTRAINT_TYPE,
0 AS ORDINAL_POSITION,
'N/A' AS COLUMN_DEFAULT,
'N/A' AS IS_NULLABLE,
'N/A' AS DATA_TYPE,
0 AS CHARACTER_MAXIMUM_LENGTH,
0 AS CHARACTER_OCTET_LENGTH,
0 AS NUMERIC_PRECISION,
0 AS NUMERIC_PRECISION_RADIX,
0 AS NUMERIC_SCALE,
0 AS DATETIME_PRECISION,
cast(ROUTINE_DEFINITION as nvarchar(4000)) AS COLLATION_NAME,
'N/A' AS EXTEND_VALUE,
CREATED AS CREATE_DATA,
LAST_ALTERED AS ALTER_DATA
FROM
INFORMATION_SCHEMA.ROUTINES
WHERE
ROUTINE_TYPE = 'PROCEDURE'
UNION ALL
--触发器
SELECT
(SELECT name FROM sysobjects WHERE ID = A.parent_obj) AS NAMES_Layer,
0 AS IS_IDENTITY,
'N/A' AS SEED_VALUE,
'N/A' AS INCREMENT_VALUE,
'N/A' AS COLUMN_NAME,
'N/A' AS DEFAULT_VALUE,
'TR' AS NAME_KIND,
A.name AS NAME_ID,
'N/A' AS CONSTARINT_TYPE,
0 AS ORDINAL_POSITION,
'N/A' AS COLUMN_DEFAULT,
'N/A' AS IS_NULLABLE,
'N/A' AS DATA_TYPE,
0 AS CHARACTER_MAXIMUM_LENGTH,
0 AS CHARACTER_OCTET_LENGTH,
0 AS NUMERIC_PRECISION,
0 AS NUMERIC_PRECISION_RADIX,
0 AS NUMERIC_SCALE,
0 AS DATETIME_PRECISION,
'N/A' AS COLLATION_NAME,
'N/A' AS EXTEND_VALUE,
getdate() AS CREATE_DATA,
getdate() AS ALTER_DATA
FROM
SYSOBJECTS AS A
WHERE
A.xtype = 'TR'
UNION ALL
--存储过程/函数参数
SELECT
DISTINCT SPECIFIC_NAME AS NAMES_Layer,
0 AS IS_IDENTITY,
'N/A' AS SEED_VALUE,
'N/A' AS INCREMENT_VALUE,
'N/A' AS COLUMN_NAME,
'N/A' AS DEFAULT_VALUE,
'PP' AS NAME_KIND,
PARAMETER_NAME AS NAME_ID,
'N/A' AS CONSTARINT_TYPE,
ORDINAL_POSITION,
'N/A' AS COLUMN_DEFAULT,
'N/A' AS IS_NULLABLE,
DATA_TYPE, --collate 排序规则 这里需要加上排序规则否则会报错
CHARACTER_MAXIMUM_LENGTH,
CHARACTER_OCTET_LENGTH,
NUMERIC_PRECISION,
NUMERIC_PRECISION_RADIX,
NUMERIC_SCALE,
DATETIME_PRECISION,
COLLATION_NAME,
'N/A' AS EXTEND_VALUE,
getdate() AS CREATE_DATA,
getdate() AS ALTER_DATA
FROM
INFORMATION_SCHEMA.PARAMETERS
WHERE
PARAMETER_MODE = 'IN'
UNION ALL
--函数
SELECT
'N/A' AS NAMES_Layer,
0 AS IS_IDENTITY,
'N/A' AS SEED_VALUE,
'N/A' AS INCREMENT_VALUE,
'N/A' AS COLUMN_NAME,
'N/A' AS DEFAULT_VALUE,
'FC' AS NAME_KIND,
SPECIFIC_NAME AS NAME_ID,
'N/A' AS CONSTRAINT_TYPE,
0 AS ORDINAL_POSITION,
'N/A' AS COLUMN_DEFAULT,
'N/A' AS IS_NULLABLE,
'N/A' AS DATA_TYPE,
0 AS CHARACTER_MAXIMUM_LENGTH,
0 AS CHARACTER_OCTET_LENGTH,
0 AS NUMERIC_PRECISION,
0 AS NUMERIC_PRECISION_RADIX,
0 AS NUMERIC_SCALE,
0 AS DATETIME_PRECISION,
--ROUTINE_DEFINITION AS COLLATION_NAME,
cast(ROUTINE_DEFINITION as nvarchar(4000)) AS COLLATION_NAME,
'N/A' AS EXTEND_VALUE,
CREATED AS CREATE_DATA,
LAST_ALTERED AS ALTER_DATA
FROM
INFORMATION_SCHEMA.ROUTINES
WHERE
ROUTINE_TYPE = 'FUNCTION'
union all
--触发器内容----------
SELECT
(SELECT name FROM sysobjects WHERE id = o.id) AS NAMES_Layer,
0 AS IS_IDENTITY,
'N/A' AS SEED_VALUE,
'N/A' AS INCREMENT_VALUE,
'N/A' AS COLUMN_NAME,
'N/A' AS DEFAULT_VALUE,
'TR_TEXT' AS NAME_KIND,
o.name AS NAME_ID,
'N/A' AS CONSTRAINT_TYPE,
0 AS ORDINAL_POSITION,
'N/A' AS COLUMN_DEFAULT,
'N/A' AS IS_NULLABLE,
'N/A' AS DATA_TYPE,
0 AS CHARACTER_MAXIMUM_LENGTH,
0 AS CHARACTER_OCTET_LENGTH,
| |
