if object_id('Proc_DropTableWithFK') is not null

 begin

drop proc dbo.Proc_DropTableWithFK

 end

GO

create proc  Proc_DropTableWithFK @PK_tableName nvarchar(50)

as

begin

declare test_cur cursor local for

select

o2.name  as 'FK_name' ,

O3.name as 'Table_Name' from sysforeignkeys  FK

inner join sys.objects o1 on FK.rkeyid=o1.object_id

inner join  sys.objects  o2 on FK.constid= o2.object_id

inner join  sys.objects  o3 on FK.fkeyid= o3.object_id

where o1.name=@PK_tableName

open test_cur

declare @FK_name nvarchar(255),@Table_Name nvarchar(255)

fetch next from test_cur into @FK_name,@Table_Name

while @@FETCH_STATUS=0

begin

    exec('ALTER TABLE '+@Table_Name+' DROP CONSTRAINT '+@FK_name)

fetch next from test_cur into @FK_name,@Table_Name

end

close test_cur

deallocate test_cur

  exec(' drop table '+@PK_tableName)

 end

 go

if object_id('Sys_User') is not null

exec dbo.Proc_DropTableWithFK 'Sys_User'

GO

create table Sys_User

(

Sys_UserID int identity(1,1) primary key,

UserAccount nvarchar(255) not null,

UserPwd nvarchar(255) not null,

UserName nvarchar(255)

)

go

if object_id('Sys_powerTeam') is not null

exec dbo.Proc_DropTableWithFK 'Sys_powerTeam'

go

create table Sys_powerTeam

(

Sys_powerTeamID int identity(1,1),

PowerTeamName nvarchar(255) not null

)

go

if object_id('Sys_PowerTeamForUser') is not null

exec dbo.Proc_DropTableWithFK 'Sys_PowerTeamForUser'

go

create table Sys_PowerTeamForUser

(

Sys_PowerTeamForUserID int identity(1,1) primary key,

Sys_powerTeamID  int,

Sys_UserID int

)

go

if object_id('Sys_Menu') is not null

exec dbo.Proc_DropTableWithFK 'Sys_Menu'

go

create table Sys_Menu

(

Sys_MenuID int identity(1,1) primary key,

Url nvarchar(255) ,

ParentId int ,

MenuName nvarchar(255) not null,

OrderNum int

)

go

if object_id('Sys_PowerTeamForMenu') is not null

exec dbo.Proc_DropTableWithFK 'Sys_PowerTeamForMenu'

go

create table Sys_PowerTeamForMenu

(

Sys_PowerTeamForMenu int identity(1,1) primary key,

Sys_powerTeamID int,

Sys_MenuID int

)

go