|
8#
楼主 |
发表于 2012-2-14 23:06:07
|
只看该作者
视图- /*************************************************
- --- 创建视图
- **************************************************/
- GO
- --------------------------------------------------
- --- 视图:vUsersAll
- --- 用途:所有用户
- --- 依赖:dbo.USysUsers/dbo.USysUserExtender/dbo._ftUSysUsers
- --------------------------------------------------
- CREATE VIEW [dbo].[vUsersAll]
- AS
- SELECT * FROM [dbo].[_ftUSysUsers](NULL, NULL)
- GO
- EXEC sp_addextendedproperty
- @name=N'MS_Description',
- @value=N'所有用户',
- @level0type=N'USER',
- @level0name=N'dbo',
- @level1type=N'VIEW',
- @level1name=N'vUsersAll'
- GO
- --------------------------------------------------
- --- 视图:vUsersSystem
- --- 用途:所有系统用户
- --- 依赖:dbo.USysUsers/dbo.USysUserExtender/dbo._ftUSysUsers
- --------------------------------------------------
- CREATE VIEW [dbo].[vUsersSystem]
- AS
- SELECT * FROM [dbo].[_ftUSysUsers](1, NULL)
- GO
- EXEC sp_addextendedproperty
- @name=N'MS_Description',
- @value=N'所有系统用户',
- @level0type=N'USER',
- @level0name=N'dbo',
- @level1type=N'VIEW',
- @level1name=N'vUsersSystem'
- GO
- --------------------------------------------------
- --- 视图:vUsersSystemCurrent
- --- 用途:当前系统用户
- --- 依赖:dbo.USysUsers/dbo.USysUserExtender/dbo._ftUSysUsers
- --------------------------------------------------
- CREATE VIEW [dbo].[vUsersSystemCurrent]
- AS
- SELECT * FROM [dbo].[_ftUSysUsers](1, 1)
- GO
- EXEC sp_addextendedproperty
- @name=N'MS_Description',
- @value=N'当前系统用户',
- @level0type=N'USER',
- @level0name=N'dbo',
- @level1type=N'VIEW',
- @level1name=N'vUsersSystemCurrent'
- GO
- --------------------------------------------------
- --- 视图:vUsersSystemDisabled
- --- 用途:当前禁用的系统用户
- --- 依赖:dbo.USysUsers/dbo.USysUserExtender/dbo._ftUSysUsers
- --------------------------------------------------
- CREATE VIEW [dbo].[vUsersSystemDisabled]
- AS
- SELECT * FROM [dbo].[_ftUSysUsers](1, 0)
- GO
- EXEC sp_addextendedproperty
- @name=N'MS_Description',
- @value=N'当前禁用的系统用户',
- @level0type=N'USER',
- @level0name=N'dbo',
- @level1type=N'VIEW',
- @level1name=N'vUsersSystemDisabled'
- GO
- --------------------------------------------------
- --- 视图:vUsersGeneral
- --- 用途:所有一般用户
- --- 依赖:dbo.USysUsers/dbo.USysUserExtender/dbo._ftUSysUsers
- --------------------------------------------------
- CREATE VIEW [dbo].[vUsersGeneral]
- AS
- SELECT * FROM [dbo].[_ftUSysUsers](0, NULL)
- GO
- --------------------------------------------------
- --- 视图:vUsersGeneralCurrent
- --- 用途:当前一般用户
- --- 依赖:dbo.USysUsers/dbo.USysUserExtender/dbo._ftUSysUsers
- --------------------------------------------------
- CREATE VIEW [dbo].[vUsersGeneralCurrent]
- AS
- SELECT * FROM [dbo].[_ftUSysUsers](0, 1)
- GO
- EXEC sp_addextendedproperty
- @name=N'MS_Description',
- @value=N'当前一般用户',
- @level0type=N'USER',
- @level0name=N'dbo',
- @level1type=N'VIEW',
- @level1name=N'vUsersGeneralCurrent'
- GO
- --------------------------------------------------
- --- 视图:vUsersGeneralDisabled
- --- 用途:当前禁用的一般用户
- --- 依赖:dbo.USysUsers/dbo.USysUserExtender/dbo._ftUSysUsers
- --------------------------------------------------
- CREATE VIEW [dbo].[vUsersGeneralDisabled]
- AS
- SELECT * FROM [dbo].[_ftUSysUsers](0, 0)
- GO
- EXEC sp_addextendedproperty
- @name=N'MS_Description',
- @value=N'当前禁用的一般用户',
- @level0type=N'USER',
- @level0name=N'dbo',
- @level1type=N'VIEW',
- @level1name=N'vUsersGeneralDisabled'
- GO
- --------------------------------------------------
- --- 视图:vGroupsAll
- --- 用途:所有用户
- --- 依赖:dbo.USysGroups/dbo._ftUSysGroups
- --------------------------------------------------
- CREATE VIEW [dbo].[vGroupsAll]
- AS
- SELECT [GID]
- ,[GroupName]
- ,[IsSystem]
- ,[Description]
- ,[Enabled]
- ,[CreateTime]
- FROM [dbo].[_ftUSysGroups](NULL, NULL)
- GO
- EXEC sp_addextendedproperty
- @name=N'MS_Description',
- @value=N'返回所有用户组',
- @level0type=N'USER',
- @level0name=N'dbo',
- @level1type=N'VIEW',
- @level1name=N'vGroupsAll'
- GO
- --------------------------------------------------
- --- 视图:vGroupsCurrent
- --- 用途:当前已启用的用户组
- --- 依赖:dbo.USysGroups/dbo._ftUSysGroups
- --------------------------------------------------
- CREATE VIEW [dbo].[vGroupsCurrent]
- AS
- SELECT [GID]
- ,[GroupName]
- ,[IsSystem]
- ,[Description]
- ,[Enabled]
- ,[CreateTime]
- FROM [dbo].[_ftUSysGroups](NULL, 1)
- GO
- EXEC sp_addextendedproperty
- @name=N'MS_Description',
- @value=N'返回当前已启用的用户组',
- @level0type=N'USER',
- @level0name=N'dbo',
- @level1type=N'VIEW',
- @level1name=N'vGroupsCurrent'
- GO
- --------------------------------------------------
- --- 视图:vGroupsSystemCurrent
- --- 用途:当前已启用的系统用户组
- --- 依赖:dbo.USysGroups/dbo._ftUSysGroups
- --------------------------------------------------
- CREATE VIEW [dbo].[vGroupsSystemCurrent]
- AS
- SELECT [GID]
- ,[GroupName]
- ,[IsSystem]
- ,[Description]
- ,[Enabled]
- ,[CreateTime]
- FROM [dbo].[_ftUSysGroups](1, 1)
- GO
- EXEC sp_addextendedproperty
- @name=N'MS_Description',
- @value=N'返回当前已启用的系统用户组',
- @level0type=N'USER',
- @level0name=N'dbo',
- @level1type=N'VIEW',
- @level1name=N'vGroupsSystemCurrent'
- GO
- --------------------------------------------------
- --- 视图:vGroupsGeneralCurrent
- --- 用途:当前已启用的一般用户组
- --- 依赖:dbo.USysGroups/dbo._ftUSysGroups
- --------------------------------------------------
- CREATE VIEW [dbo].[vGroupsGeneralCurrent]
- AS
- SELECT [GID]
- ,[GroupName]
- ,[IsSystem]
- ,[Description]
- ,[Enabled]
- ,[CreateTime]
- FROM [dbo].[_ftUSysGroups](0, 1)
- GO
- EXEC sp_addextendedproperty
- @name=N'MS_Description',
- @value=N'返回当前已启用的一般用户组',
- @level0type=N'USER',
- @level0name=N'dbo',
- @level1type=N'VIEW',
- @level1name=N'vGroupsGeneralCurrent'
- GO
- --------------------------------------------------
- --- 视图:vUsersGroupsCurrent
- --- 用途:返回所有当前用户的用户组
- --- 依赖:dbo.USysUsers
- --- dbo.USysGroups
- --- dbo.USysGroupUsers
- --- dbo._ftUSysUsers
- --- dbo._ftUSysGroups
- --------------------------------------------------
- CREATE VIEW [dbo].[vUsersGroupsCurrent] AS
- SELECT u.[UID]
- , u.[UserName]
- , u.[Password]
- , u.[IsSystem]
- , u.[Description]
- , u.[CreateTime]
- , u.[RealName]
- , u.[Gender]
- , u.[Birthday]
- , u.[Email]
- , u.[Company]
- , u.[Post]
- , u.[Title]
- , u.[Mobile]
- , u.[Tel]
- , u.[Province]
- , u.[City]
- , u.[ZipCode]
- , g.[GID]
- , g.[GroupName]
- , g.[IsSystem] AS g_IsSystem
- , g.[Description] AS g_Description
- , g.[Enabled] AS g_Enabled
- FROM [dbo].[_ftUSysUsers](NULL, 1) AS u
- LEFT OUTER JOIN [dbo].[USysGroupUsers] AS ug ON u.[UID] = ug.[UID]
- LEFT OUTER JOIN [dbo].[_ftUSysGroups](NULL, 1) AS g ON ug.[GID] = g.[GID]
- GO
- EXEC sp_addextendedproperty
- @name=N'MS_Description',
- @value=N'返回所有当前用户的用户组',
- @level0type=N'USER',
- @level0name=N'dbo',
- @level1type=N'VIEW',
- @level1name=N'vUsersGroupsCurrent'
- GO
- --------------------------------------------------
- --- 视图:vGroupsUsersCurrent
- --- 用途:返回所有当前用户组的用户
- --- 依赖:dbo.USysUsers
- --- dbo.USysGroups
- --- dbo.USysGroupUsers
- --- dbo._ftUSysUsers
- --- dbo._ftUSysGroups
- --------------------------------------------------
- CREATE VIEW [dbo].[vGroupsUsersCurrent] AS
- SELECT g.[GID]
- , g.[GroupName]
- , g.[IsSystem]
- , g.[Description]
- , g.[Enabled]
- , u.[UID]
- , u.[UserName]
- , u.[Password]
- , u.[IsSystem] AS u_IsSystem
- , u.[Description] AS u_Description
- , u.[CreateTime]
- , u.[RealName]
- , u.[Gender]
- , u.[Birthday]
- , u.[Email]
- , u.[Company]
- , u.[Post]
- , u.[Title]
- , u.[Mobile]
- , u.[Tel]
- , u.[Province]
- , u.[City]
- , u.[ZipCode]
- FROM [dbo].[_ftUSysUsers](NULL, 1) AS u
- RIGHT OUTER JOIN [dbo].[USysGroupUsers] AS ug ON u.[UID] = ug.[UID]
- RIGHT OUTER JOIN [dbo].[_ftUSysGroups](NULL, 1) AS g ON ug.[GID] = g.[GID]
- GO
- EXEC sp_addextendedproperty
- @name=N'MS_Description',
- @value=N'返回所有当前用户组的用户',
- @level0type=N'USER',
- @level0name=N'dbo',
- @level1type=N'VIEW',
- @level1name=N'vGroupsUsersCurrent'
- GO
复制代码 |
|