objectsbrowser.pas

mssql查询分析器

    Rst.MoveNext;
  end;

  Node.Expand(False);
end;

procedure TObjectsBrowserPanel.FillDepends(Node: TTreeNode;
  DBName: string; ObjID: Integer);
const
  sql1 =
'select distinct owner = user_name(o.uid), o.name, o.type, d.id ' +
'from [%s].dbo.sysdepends d, [%s].dbo.sysobjects o ' +
'where d.depid = %d and d.id = o.id ';

  sql2 =
'select distinct owner = user_name(o.uid), o.name, o.type, o.id ' +
'from [%s].dbo.sysobjects o, [%s].dbo.sysdepends d ' +
'where d.id = %d and o.id = d.depid';

var
  Rst: _Recordset;
  dNode: TTreeNode;
  dbo, dname: string;
begin
  ExecuteSql(Format('use [%s]', [DBName]));

  // 引用自身的其它对象
  Rst := ExecuteRst(Format(sql1, [DBName, DBName, ObjID]));
  while not Rst.EOF do
  begin
    dname := Rst.Fields.Item['name'].Value;
    dbo := Rst.Fields.Item['owner'].Value;

    dNode := FTreeView.Items.AddChild(Node, dbo + '.' + dname);
    dNode.ImageIndex := 8;
    dNode.SelectedIndex := 8;

    Rst.MoveNext;
  end;

  // 被自身引用的其它对象
  Rst := ExecuteRst(Format(sql2, [DBName, DBName, ObjID]));
  while not Rst.EOF do
  begin
    dname := Rst.Fields.Item['name'].Value;
    dbo := Rst.Fields.Item['owner'].Value;

    dNode := FTreeView.Items.AddChild(Node, dbo + '.' + dname);
    dNode.ImageIndex := 9;
    dNode.SelectedIndex := 9;

    Rst.MoveNext;
  end;

  if Node.Count = 0 then Node.HasChildren := False;
{
select distinct owner = user_name(o.uid), o.name, o.type, d.id
from [pubs].dbo.sysdepends d, [pubs].dbo.sysobjects o
where d.depid = 213575799 and d.id = o.id

select distinct owner = user_name(o.uid), o.name, o.type, o.id
from [pubs].dbo.sysobjects o, [pubs].dbo.sysdepends d
where d.id = 213575799 and o.id = d.depid
}
end;

procedure TObjectsBrowserPanel.FillFunctions(Node: TTreeNode;
  dbName: string);
  
  procedure AddFuncItemNode(ParentNode: TTreeNode; Title: string;
                                Data: TFuncItemInfo);
  var
    Node: TTreeNode;
  begin
    Node := FTreeView.Items.AddChildObject(ParentNode, Title, Pointer(Data));
    Node.ImageIndex := 2;
    Node.SelectedIndex := 3;
    Node.HasChildren := True;
  end;

const
  sql =
'select id, owner = user_name(uid), name, type ' +
'from [%s].dbo.sysobjects ' +
'where type = N''TF'' or type = N''IF'' or type = N''FN'' order by name';
var
  Rst: _Recordset;
  dbo, fnname: string;
  fnID: Integer;
  fnNode: TTreeNode;
begin
  ExecuteSql(Format('use [%s]', [dbName]));
  Rst := ExecuteRst(Format(sql, [dbName]));
  while not Rst.EOF do
  begin
    dbo := String(Rst.Fields.Item['owner'].Value);
    fnname := String(Rst.Fields.Item['name'].Value);
    fnID := Integer(Rst.Fields.Item['id'].Value);

    fnNode := FTreeView.Items.AddChild(Node, dbo + '.' + fnname);
    fnNode.ImageIndex := 14;
    fnNode.SelectedIndex := 14;
    fnNode.Data := Pointer(TFuncNode.Create);

    AddFuncItemNode(fnNode, '参数',
        TFuncItemInfo.Create(dbName, dbo, fnname, fnID, ftParameter));
    AddFuncItemNode(fnNode, '相关性',
        TFuncItemInfo.Create(dbName, dbo, fnname, fnID, ftDepend));

    Rst.MoveNext;
  end;
  if Node.Count = 0 then Node.HasChildren := False;
end;

procedure TObjectsBrowserPanel.FillSProcParams(Node: TTreeNode;
  dbName, dbo, spname: string);
const
  sql =
'exec sp_sproc_columns N%s, N%s, N%s, NULL, @ODBCVer = 3';
var
  Rst: _Recordset;
  column, typename: string;
  coltype: Integer;
  parNode: TTreeNode;

  function GetParamDisplayText: string;
  var
    s: string;
  begin
    Result := column + '(' + typename + ', ';
    case coltype of
      1: s := '输入';
      2: s := '输入/输出';
      3, 5: s := '返回值';
      4: s := '输出';
      else s := '未知';
    end;
    Result := Result + s + ')';
  end;
begin
  ExecuteSql(Format('use [%s]', [dbName]));
  Rst := ExecuteRst(Format(sql,
              [QuotedStr(spname), QuotedStr(dbo), QuotedStr(dbName)]));
  while not Rst.EOF do
  begin
    column := Rst.Fields['COLUMN_NAME'].Value;
    typename := Rst.Fields['TYPE_NAME'].Value;
    coltype := Rst.Fields['COLUMN_TYPE'].Value;

    parNode := FTreeView.Items.AddChild(Node, GetParamDisplayText);
    parNode.ImageIndex := 13;
    parNode.SelectedIndex := 13;

    Rst.MoveNext;
  end;
  if Node.Count = 0 then Node.HasChildren := False;
end;

procedure TObjectsBrowserPanel.FillStoredProcs(Node: TTreeNode;
  dbName: string);

  procedure AddSPItemNode(ParentNode: TTreeNode; Title: string;
                                Data: TSPItemInfo);
  var
    Node: TTreeNode;
  begin
    Node := FTreeView.Items.AddChildObject(ParentNode, Title, Pointer(Data));
    Node.ImageIndex := 2;
    Node.SelectedIndex := 3;
    Node.HasChildren := True;
  end;

const
  sql =
'select id, owner = user_name(uid), name, status ' +
'from [%s].dbo.sysobjects ' +
'where type = N''P'' order by name';
var
  Rst: _Recordset;
  dbo, spname: string;
  spID: Integer;
  spNode: TTreeNode;
begin
  ExecuteSql(Format('use [%s]', [dbName]));
  Rst := ExecuteRst(Format(sql, [dbName]));
  while not Rst.EOF do
  begin
    dbo := String(Rst.Fields.Item['owner'].Value);
    spname := String(Rst.Fields.Item['name'].Value);
    spID := Integer(Rst.Fields.Item['id'].Value);

    spNode := FTreeView.Items.AddChild(Node, dbo + '.' + spname);
    spNode.ImageIndex := 12;
    spNode.SelectedIndex := 12;
    spNode.Data := Pointer(TSPNode.Create);

    AddSPItemNode(spNode, '参数',
        TSPItemInfo.Create(dbName, dbo, spname, spID, stParameter));
    AddSPItemNode(spNode, '相关性',
        TSPItemInfo.Create(dbName, dbo, spname, spID, stDepend));

    Rst.MoveNext;
  end;
  if Node.Count = 0 then Node.HasChildren := False;
end;

procedure TObjectsBrowserPanel.FillSysTables(Node: TTreeNode;
  dbName: string);

  procedure AddTableItemNode(ParentNode: TTreeNode; Title: string;
                                Data: TTableItemInfo);
  var
    Node: TTreeNode;
  begin
    Node := FTreeView.Items.AddChildObject(ParentNode, Title, Pointer(Data));
    Node.ImageIndex := 2;
    Node.SelectedIndex := 3;
    Node.HasChildren := True;
  end;

const
  sql = 'select id, owner = user_name(uid), name, status ' +
        'from [%s].dbo.sysobjects where type = N''S'' order by name';
var
  Rst: _Recordset;
  tabNode: TTreeNode;
  dbo, table: string;
  tabID: Integer;
begin
  ExecuteSql(Format('use [%s]', [dbName]));
  Rst := ExecuteRst(Format(sql, [dbName]));
  while not Rst.EOF do
  begin
    dbo := String(Rst.Fields.Item['owner'].Value);
    table := String(Rst.Fields.Item['name'].Value);
    tabID := Integer(Rst.Fields.Item['id'].Value);

    tabNode := FTreeView.Items.AddChild(Node, dbo + '.' + table);
    tabNode.ImageIndex := 4;
    tabNode.SelectedIndex := 4;
    tabNode.Data := Pointer(TTableNode.Create);

    AddTableItemNode(tabNode, '列', TTableItemInfo.Create(dbName, tabID, ttColumn));
    AddTableItemNode(tabNode, '索引', TTableItemInfo.Create(dbName, tabID, ttIndex));
    AddTableItemNode(tabNode, '约束', TTableItemInfo.Create(dbName, tabID, ttContraint));
    AddTableItemNode(tabNode, '相关性', TTableItemInfo.Create(dbName, tabID, ttDepend));
    AddTableItemNode(tabNode, '触发器', TTableItemInfo.Create(dbName, tabID, ttTrigger));

    Rst.MoveNext;
  end;
  if Node.Count = 0 then Node.HasChildren := False;
end;

procedure TObjectsBrowserPanel.FillTableColumns(Node: TTreeNode;
  Data: TObject);
const
  sql =
'select C.name, C.type, C.xtype, C.xusertype, ' +
'    C.length, C.prec, C.scale, C.isnullable, ' +
'    typename = T.name ' +
'from [%s].dbo.syscolumns C, [%s].dbo.systypes T ' +
'where C.xusertype = T.xusertype ' +
'    and id = %d order by C.colorder ';
var
  info: TTableItemInfo;
  Rst: _Recordset;
  cNode: TTreeNode;
  title, cname, typename: string;
  prec: SmallInt;
  scale: ShortInt;
  length: Integer;
  nullable: Boolean;
begin
  info := Data as TTableItemInfo;

  ExecuteSql(Format('use [%s]', [info.FDBName]));
  Rst := ExecuteRst(Format(sql, [info.FDBName, info.FDBName, info.FTableID]));
  while not Rst.EOF do
  begin
    cname := Rst.Fields.Item['name'].Value;
    typename := Rst.Fields.Item['typename'].Value;
    prec := Rst.Fields.Item['prec'].Value;
    scale := Rst.Fields.Item['scale'].Value;
    length := Rst.Fields.Item['length'].Value;
    nullable := Rst.Fields.Item['isnullable'].Value;

    title := GetColumnDescription(cname, typename, length, prec, scale, nullable);

    cNode := FTreeView.Items.AddChild(Node, title);
    cNode.ImageIndex := 5;
    cNode.SelectedIndex := 5;
    
    Rst.MoveNext;
  end;

  if Node.Count = 0 then Node.HasChildren := False;
{
USE [LerpSys]
go
select name, type, xtype, xusertype, length, prec, scale, isnullable from [LerpSys].dbo.syscolumns where id = 1509580416 order by colorder
go
select xtype, xusertype, type, name from [LerpSys].dbo.systypes order by xtype
go
select xtype, xusertype, type, name from [LerpSys].dbo.systypes where xusertype > 256 order by xtype
go
}
end;

procedure TObjectsBrowserPanel.FillTableContraints(Node: TTreeNode;
  Data: TObject);
const
  sql =
'select id, owner = user_name(uid), name, type, info, xtype ' +
'from [%s].dbo.sysobjects ' +
'where parent_obj = %d ' +
'  and (type = N''C'' or type = N''D'' or type = N''F'' or type = N''K'')';
var
  info: TTableItemInfo;
  Rst: _Recordset;
  cNode: TTreeNode;
  cname, dbo: string;
begin
  info := Data as TTableItemInfo;

  ExecuteSql(Format('use [%s]', [info.FDBName]));
  Rst := ExecuteRst(Format(sql, [info.FDBName, info.FTableID]));
  while not Rst.EOF do
  begin
    dbo := Rst.Fields.Item['owner'].Value;
    cname := Rst.Fields.Item['name'].Value;
    cNode := FTreeView.Items.AddChild(Node, dbo + '.' + cname);
    cNode.ImageIndex := 7;
    cNode.SelectedIndex := 7;
    
    Rst.MoveNext;
  end;

  if Node.Count = 0 then Node.HasChildren := False;
end;

procedure TObjectsBrowserPanel.FillTableIndexes(Node: TTreeNode;
  Data: TObject);
const
  sql =
'select I.name, I.status from [%s].dbo.sysindexes I ' +
'where I.id = %d and I.indid > 0 and I.indid < 255 ' +
'  and INDEXPROPERTY(I.id, I.name, N''IsStatistics'') = 0 ' +
'  and INDEXPROPERTY(I.id, I.name, N''IsHypothetical'') = 0 ' +
'  and I.name not in ' +
'    ( ' +
'    select O.name from sysobjects O ' +
'    where O.parent_obj = I.id and OBJECTPROPERTY(O.id, N''isConstraint'') = 1 ' +
'    )';

var
  info: TTableItemInfo;
  Rst: _Recordset;
  idxNode: TTreeNode;
  idxname: string;
begin
  info := Data as TTableItemInfo;

  ExecuteSql(Format('use [%s]', [info.FDBName]));
  Rst := ExecuteRst(Format(sql, [info.FDBName, info.FTableID]));
  while not Rst.EOF do
  begin
    idxname := Rst.Fields.Item['name'].Value;
    idxNode := FTreeView.Items.AddChild(Node, idxname);
    idxNode.SelectedIndex := 6;
    idxNode.ImageIndex := 6;
    
    Rst.MoveNext;
  end;

  if Node.Count = 0 then Node.HasChildren := False;
end;

procedure TObjectsBrowserPanel.FillTriggers(Node: TTreeNode;
  DBName: string; ObjID: Integer);
const
  sql =
'select name, owner = user_name(uid) ' +
'from [%s].dbo.sysobjects ' +
'where type = N''TR'' and parent_obj = %d';

var
  Rst: _Recordset;
  trgNode: TTreeNode;
  dbo, trigger: string;
begin
  ExecuteSql(Format('use [%s]', [DBName]));
  Rst := ExecuteRst(Format(sql, [DBName, ObjID]));
  while not Rst.EOF do
  begin
    dbo := Rst.Fields.Item['owner'].Value;
    trigger := Rst.Fields.Item['name'].Value;

    trgNode := FTreeView.Items.AddChild(Node, dbo + '.' + trigger);
    trgNode.ImageIndex := 10;
    trgNode.SelectedIndex := 10;
    
    Rst.MoveNext;
  end;

  if Node.Count = 0 then Node.HasChildren := False;
{
select name, owner = user_name(uid)
from [pubs].dbo.sysobjects
where type = N'TR' and parent_obj = 213575799
}
end;

procedure TObjectsBrowserPanel.FillUserTables(Node: TTreeNode;
  dbName: string);

  procedure AddTableItemNode(ParentNode: TTreeNode; Title: string;
                                Data: TTableItemInfo);