delphi 面试题

Q: 现有MS SQL Server 数据库 UserLibs 列举出所有用户表及其字段。

A：

use UserLibs

declare @name nvarchar(60)
declare @colname nvarchar(60)
declare @id int
declare @colnames nvarchar(3000)

declare csr_UsrTable cursor for 
select name,id from sysobjects where objectproperty(id, N'IsUserTable') = 1 order by name

open csr_UsrTable

fetch next from csr_UsrTable into @name,@id

while (@@FETCH_STATUS = 0)
begin
  set @colnames = ''

  --查询该表下的所有字段
  declare csr_UsrColumn cursor for 
  select name from syscolumns where id = @id

  open csr_UsrColumn

  fetch next from csr_UsrColumn into @colname

  while (@@FETCH_STATUS = 0)
  begin
    if @colnames <> '' 
      set @colnames = @colnames + ',' + @colname
    else
      set @colnames = @colname

    fetch next from csr_UsrColumn into @colname
  end

  close csr_UsrColumn
  deallocate csr_UsrColumn

  print '表格名为【' + @name + '】下的所有字段为：' + @colnames

  fetch next from csr_UsrTable into @name,@id
end


close csr_UsrTable
deallocate csr_UsrTable