SQLServer的日常维护语句 - 数据库编程

('+TBR.column_name+')' as '新建外键索引,复制然后在运行' FROM FK INNER JOIN TB TBP ON FK.parent_object_id = TBP.object_id AND FK.parent_column_id = TBP.column_id INNER JOIN TB TBR ON FK.referenced_object_id = TBR.object_id AND FK.referenced_column_id = TBR.column_id /* 新建外键索引,复制然后在运行 alter table [dbo].[tb_c] add constraint [FK__tb_c__idd__0A9D95DB] foreign key(idd) references [dbo].[tb_b](idd) alter table [dbo].[tb_b] add constraint [FK__tb_b__id__05D8E0BE] foreign key(id) references [dbo].[tb](id) */
另外，还有一个问题：原来两个表之间是有外键的，删除了外键导入数据后，导入的数据记录条数和原表也一致，发现子表有记录不属于主表的，那么原来的外键是怎么建立的？

create table tb(id int primary key ,vv varchar(10))

insert into tb
values(1,'aa') 
go
 
create table tb_b(
idd int primary key,
id int --foreign key references tb(id)
)

insert into tb_b
values(1,2)  --id不在主表中
go

--新增外键约束,不会报错，with nocheck对于之前已经存在的数据，不会进行检测
ALTER TABLE [dbo].[tb_b]  WITH noCHECK ADD FOREIGN KEY([id])
REFERENCES [dbo].[tb] ([id])
GO

 
--会报错 ，在建立上面的约束后，再次插入，就会报错了
insert into tb_b
values(2,2)  --id不在主表中

2、如何根据表名查询出创建该表的代码

--当用以下代码创建一个表后，如何根据表名查询出创建该表的代码(也就是以下代码)？
CREATE TABLE [dbo].[a1](
	[c2] [decimal](10, 2) NULL,
	[c3] [decimal](10, 3) NULL CONSTRAINT [DF_a1_c3]  DEFAULT ((0)),
	[re] [bigint] IDENTITY(1,1) NOT NULL,
 CONSTRAINT [PK_a1] PRIMARY KEY CLUSTERED 
(
	[re] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
go

declare @sql varchar(8000),@tablename varchar(100)
set @tablename ='a1'--这里输入表名
set @sql = 'create table ['+@tablename+'] 
(
'
select @sql = @sql + b.name + ' '+
       c.name+
       case when c.collation_name is not null then '('+
         case when b.max_length <>




-1 then convert(varchar(100),b.max_length)
           else 'MAX'
         end +') '
        else ''
       end +
       case when b.is_identity = 1 then ' identity('+convert(varchar(100),IDENT_SEED(@tablename))+','+convert(varchar(100),IDENT_INCR(@tablename))+')' else '' end +
       case when d.definition is not null then ' default('+d.definition +')' else '' end +
       case when b.is_nullable = 0 then ' not null' else ' null' end +
       
',
'
from sys.objects a join sys.columns b
on a.object_id = b.object_id
join sys.types c
on b.system_type_id = c.system_type_id and b.user_type_id = c.user_type_id
left join sys.default_constraints d
on b.default_object_id = d.object_id
where a.name=@tablename
order by b.column_id
if exists(select * from sys.indexes where object_id =object_id(@tablename) and is_primary_key =1 )
begin
select @sql = @sql + 'CONSTRAINT ['+name+'] PRIMARY KEY '+type_desc+'
(
' from sys.indexes where object_id =object_id(@tablename) and is_primary_key =1
select @sql = @sql + b.name + case when a.is_descending_key =1 then ' DESC' else ' ASC' end +',
' from sys.index_columns a join sys.columns b
on a.object_id= b.object_id and a.column_id = b.column_id
where a.object_id =object_id(@tablename)
select @sql = left(@sql,len(@sql)-3)+'
)'
select @sql = @sql+'
) ON [PRIMARY] '
end
else
begin
select @sql = left(@sql,len(@sql)-1)+'
) ON [PRIMARY] '
end
print @sql

/*
create table [a1] 
(
c2 decimal null,
c3 decimal default(((0))) null,
re bigint identity(1,1) not null,
CONSTRAINT [PK_a1] PRIMARY KEY CLUSTERED
(
re ASC
)
) ON [PRIMARY] 

*/

SQLServer的日常维护语句(二)