检索数据表字段列表（SQL）

--数据导入--

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

--删除存储过程--
if (exists(select * from sysobjects where xtype='P' and name='ext_test'))
begin
 drop procedure ext_test
end
go
--新建存储过程--
create procedure ext_test(
 @FTblName nvarchar(50),  --外键表名
 @SourceLinkServer nvarchar(50), --源数据库服务器连接
 @SourceDBName nvarchar(50), --源数据库名称
 @DateBegin datetime,  --开始时间
 @DateEnd datetime,  --结束时间
 @del int   --删除原数据标记
 )
as
 declare @sql nvarchar(500)
 declare @TblName nvarchar(100)
 declare @SourTbl nvarchar(255)
 declare @AllFields nvarchar(4000)
 set @AllFields=''

 --通过游标获取用户数据表
 DECLARE @GetTableNames CURSOR
 if @FTblName is null
  set @GetTableNames = CURSOR LOCAL FOR
   select name from sysobjects where xtype='U'
 else
  set @GetTableNames = CURSOR LOCAL FOR
   select name from sysobjects where xtype='U'
    and object_id(name) in(
    select rkeyid from sysforeignkeys where fkeyid=object_id(@FTblName)
   )
 

 OPEN @GetTableNames
 FETCH NEXT FROM @GetTableNames
 INTO
 @TblName
 WHILE @@FETCH_STATUS = 0
 BEGIN
  declare @Fields nvarchar(2000)
  set @Fields=''
  --通过游标获取用户数据表字段
  DECLARE @GetTableFields CURSOR
  DECLARE @FieldName nvarchar(50)
  set @GetTableFields = CURSOR LOCAL FOR
   select name from syscolumns where id=object_id(@TblName)
  
  OPEN @GetTableFields
  FETCH NEXT FROM @GetTableFields
  INTO
  @FieldName
  WHILE @@FETCH_STATUS = 0
  BEGIN
   set @Fields = @Fields + ',' + @FieldName
   FETCH NEXT FROM @GetTableFields
   INTO
   @FieldName
  END
  CLOSE @GetTableFields
  DEALLOCATE @GetTableFields 
  set @Fields = SUBSTRING(@Fields,2,len(@Fields))
  set @Fields = 'insert into ' + @TblName + '(' + @Fields + ') '
  print @Fields

  
  FETCH NEXT FROM @GetTableNames
  INTO
  @TblName
 END

 CLOSE @GetTableNames
 DEALLOCATE @GetTableNames

return

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

 

-- @FTblName nvarchar(50),  --外键表名
-- @SourceLinkServer nvarchar(50), --源数据库服务器连接
-- @SourceDBName nvarchar(50), --源数据库名称
-- @DateBegin datetime,  --开始时间
-- @DateEnd datetime,  --结束时间
-- @del int   --删除原数据标记
--测试--
--SET NOCOUNT ON
go
exec ext_CreateLinkServer 'ITSV','192.168.0.177','trafax50_2','temp','000000'
go
CREATE TABLE #HasDealWithTable (TblName nvarchar(250))
GO
--exec ext_ChangeTableIdentity 1
go
exec ext_test null,'ITSV','TraFax50_2',null,'2006-12-31',1
go
--exec ext_ChangeTableIdentity 0
go
select * from #HasDealWithTable
go
drop table #HasDealWithTable
go
--select * from ITSV.trafax50.dbo.Analysisreport
exec ext_DropLinkServer 'ITSV'
go

--SET NOCOUNT OFF
go