我和一个同事写了这个存储过程,用wiki标记为screwturn wiki系统记录了一个数据库表。原来我写的时候没有光标,因为直到今天我都不知道怎么用!
我从基本上是你在下面看到的组合开始。我将为每行选择一列,其中该列是该行的整个wikimarkup。这非常有效,但我想在结果前后打印文本。我用了几个工会来解决这个问题。我会将标题与结果集合并,然后将所有内容与页脚合并。但是之后,我必须在每一行之间插入一行文本,这是我在不使用光标的情况下找不到的部分。简而言之:
如何在每个结果行之前选择一组具有硬编码行的记录?
在我的例子中,每行前面都需要有一个
|-
行。
set ansi_nulls on
go
set quoted_identifier on
go
alter procedure DocTable
@TableName varchar(256)
as
begin
set nocount on;
declare @WikiDocData table
(
Name nvarchar(256),
[Type] nvarchar(256),
Nullable nvarchar(256),
[Default] nvarchar(256),
[Identity] nvarchar(256),
[Description] nvarchar(max)
)
insert into @WikiDocData
select
c.name as Name,
tp.name +
' (' +
(case when c.max_length = -1 then 'MAX' else convert(nvarchar(256),c.max_length) end) +
', ' +
convert(nvarchar(256), c.scale) +
', ' +
convert(nvarchar(256), c.[precision]) + ')'
as [Type (L,S,P)],
(case when c.is_nullable = 1 then 'Yes' else '' end) as Nullable,
isnull(d.[definition], '') as [Default],
(case when c.is_identity = 1 then 'Yes' else '' end) as [Identity],
convert(nvarchar(max),isnull(p.value, '')) as [Description]
from
sys.tables t
inner join sys.columns c on t.object_id = c.object_id
left join sys.extended_properties p on c.object_id = p.major_id and c.column_id = p.minor_id
inner join sys.types tp on c.system_type_id = tp.system_type_id
left join sys.default_constraints d on c.default_object_id = d.object_id and c.column_id = d.parent_column_id
where
t.[name] = @TableName
and tp.name <> 'sysname'
order by
t.object_id,
c.column_id
/* Dear reader, if you know how to do this without a cursor, please let me know! */
-- Output header
print '{| cellpadding="4" cellspacing="0" border="1"'
print '! Name !! Type (L,S,P) !! Nullable !! Default !! Identity !! Description'
-- Output each row and row separator
declare @WikiRow nvarchar(max)
declare @GetWikiRow cursor
set @GetWikiRow = cursor for
select
'| ' +
Name + ' || ' +
[Type] + ' || ' +
Nullable + ' || ' +
[Default] + ' || ' +
[Identity] + ' || ' +
[Description]
from
@WikiDocData
open @GetWikiRow fetch next from @GetWikiRow into @WikiRow while @@fetch_status = 0
begin
print '|-'
print @WikiRow
fetch next from @GetWikiRow into @WikiRow
end
close @GetWikiRow
deallocate @GetWikiRow
-- Output footer
print '|}'
end
go
目前正在工作。在aspnet_成员身份上运行时,它只打印以下内容:
{| cellpadding="4" cellspacing="0" border="1"
! Name !! Type (L,S,P) !! Nullable !! Default !! Identity !! Description
|-
| ApplicationId || uniqueidentifier (16, 0, 0) || || || ||
|-
| UserId || uniqueidentifier (16, 0, 0) || || || ||
|-
| Password || nvarchar (256, 0, 0) || || || ||
|-
| PasswordFormat || int (4, 0, 10) || || ((0)) || ||
|-
| PasswordSalt || nvarchar (256, 0, 0) || || || ||
|-
| MobilePIN || nvarchar (32, 0, 0) || Yes || || ||
|-
| Email || nvarchar (512, 0, 0) || Yes || || ||
|-
| LoweredEmail || nvarchar (512, 0, 0) || Yes || || ||
|-
| PasswordQuestion || nvarchar (512, 0, 0) || Yes || || ||
|-
| PasswordAnswer || nvarchar (256, 0, 0) || Yes || || ||
|-
| IsApproved || bit (1, 0, 1) || || || ||
|-
| IsLockedOut || bit (1, 0, 1) || || || ||
|-
| CreateDate || datetime (8, 3, 23) || || || ||
|-
| LastLoginDate || datetime (8, 3, 23) || || || ||
|-
| LastPasswordChangedDate || datetime (8, 3, 23) || || || ||
|-
| LastLockoutDate || datetime (8, 3, 23) || || || ||
|-
| FailedPasswordAttemptCount || int (4, 0, 10) || || || ||
|-
| FailedPasswordAttemptWindowStart || datetime (8, 3, 23) || || || ||
|-
| FailedPasswordAnswerAttemptCount || int (4, 0, 10) || || || ||
|-
| FailedPasswordAnswerAttemptWindowStart || datetime (8, 3, 23) || || || ||
|-
| Comment || ntext (3000, 0, 0) || Yes || || ||
|}
带有LittleBobbytables答案的新代码(它较短,但涉及大量字符串连接,并且在标记中有超过8000个字符时无法打印):
set ansi_nulls on
go
set quoted_identifier on
go
alter procedure DocTable
@TableName varchar(256)
as
begin
set nocount on;
-- Output header
print '{| cellpadding="4" cellspacing="0" border="1"'
-- Output each row and row separator
declare @WikiRow nvarchar(max)
set @WikiRow = '! Name !! Type (L,S,P) !! Nullable !! Default !! Identity !! Description'
select
@WikiRow = @WikiRow +
char(10) + '|- ' + char(10) + '| ' +
c.name + ' || ' +
tp.name +
' (' +
(case when c.max_length = -1 then 'MAX' else convert(nvarchar(256),c.max_length) end) +
', ' +
convert(nvarchar(256), c.scale) +
', ' +
convert(nvarchar(256), c.[precision]) + ')' + ' || ' +
(case when c.is_nullable = 1 then 'Yes' else '' end) + ' || ' +
isnull(d.[definition], '') + ' || ' +
(case when c.is_identity = 1 then 'Yes' else '' end) + ' || ' +
convert(nvarchar(max),isnull(p.value, ''))
from
sys.tables t
inner join sys.columns c on t.object_id = c.object_id
left join sys.extended_properties p on c.object_id = p.major_id and c.column_id = p.minor_id
inner join sys.types tp on c.system_type_id = tp.system_type_id
left join sys.default_constraints d on c.default_object_id = d.object_id and c.column_id = d.parent_column_id
where
t.[name] = @TableName
and tp.name <> 'sysname'
order by
t.object_id,
c.column_id
print @WikiRow
-- Output footer
print '|}'
end
go
