Wednesday, September 23, 2015

Generate SQL Statements to Group By Each Column of Table Separately

When working with data with the intent to visualize, there are times when I'll want to group by every column in a table, separately, one at a time, to determine the possible values in that column.

I do this often enough I created a simple SQL Query to build these group by statements for me:


declare @table_name varchar(200) = 'dbo.mytablename'
select
'select ' + c.name + ', count(1) cnt from ' + @table_name + ' group by ' + c.name + ' order by 2 '
from sys.columns c
where c.object_id = object_id(@table_name)


Hope this helps,
Aaron





Friday, September 18, 2015

ASP.NET MVC 5 Identity, Users, Roles, Accounts, SQL to Generate Tables

In ASP.NET MVC 5, user management changed significantly from ASP.NET MVC 4.

This is the start of a series of blog posts on the topic (long overdue).

This post is on the SQL that your website executes to initially create the identity (user, role) tables.

The SQL the ApplicationUserManager and ApplicationSigninManager generate and execute is below:



CREATE TABLE [dbo].[AspNetRoles] (
    [Id] [nvarchar](128) NOT NULL,
    [Name] [nvarchar](256) NOT NULL,
    CONSTRAINT [PK_dbo.AspNetRoles] PRIMARY KEY ([Id])
)
go
CREATE TABLE [dbo].[AspNetUserRoles] (
    [UserId] [nvarchar](128) NOT NULL,
    [RoleId] [nvarchar](128) NOT NULL,
    CONSTRAINT [PK_dbo.AspNetUserRoles] PRIMARY KEY ([UserId], [RoleId])
)
go
CREATE TABLE [dbo].[AspNetUsers] (
    [Id] [nvarchar](128) NOT NULL,
    [Email] [nvarchar](256),
    [EmailConfirmed] [bit] NOT NULL,
    [PasswordHash] [nvarchar](max),
    [SecurityStamp] [nvarchar](max),
    [PhoneNumber] [nvarchar](max),
    [PhoneNumberConfirmed] [bit] NOT NULL,
    [TwoFactorEnabled] [bit] NOT NULL,
    [LockoutEndDateUtc] [datetime],
    [LockoutEnabled] [bit] NOT NULL,
    [AccessFailedCount] [int] NOT NULL,
    [UserName] [nvarchar](256) NOT NULL,
    CONSTRAINT [PK_dbo.AspNetUsers] PRIMARY KEY ([Id])
)
go
CREATE TABLE [dbo].[AspNetUserClaims] (
    [Id] [int] NOT NULL IDENTITY,
    [UserId] [nvarchar](128) NOT NULL,
    [ClaimType] [nvarchar](max),
    [ClaimValue] [nvarchar](max),
    CONSTRAINT [PK_dbo.AspNetUserClaims] PRIMARY KEY ([Id])
)
go
CREATE TABLE [dbo].[AspNetUserLogins] (
    [LoginProvider] [nvarchar](128) NOT NULL,
    [ProviderKey] [nvarchar](128) NOT NULL,
    [UserId] [nvarchar](128) NOT NULL,
    CONSTRAINT [PK_dbo.AspNetUserLogins] PRIMARY KEY ([LoginProvider], [ProviderKey], [UserId])
)
go
CREATE UNIQUE INDEX [RoleNameIndex] ON [dbo].[AspNetRoles]([Name])
go
CREATE INDEX [IX_UserId] ON [dbo].[AspNetUserRoles]([UserId])
go
CREATE INDEX [IX_RoleId] ON [dbo].[AspNetUserRoles]([RoleId])
go
CREATE UNIQUE INDEX [UserNameIndex] ON [dbo].[AspNetUsers]([UserName])
go
CREATE INDEX [IX_UserId] ON [dbo].[AspNetUserClaims]([UserId])
go
CREATE INDEX [IX_UserId] ON [dbo].[AspNetUserLogins]([UserId])
go
ALTER TABLE [dbo].[AspNetUserRoles] ADD CONSTRAINT [FK_dbo.AspNetUserRoles_dbo.AspNetRoles_RoleId] FOREIGN KEY ([RoleId]) REFERENCES [dbo].[AspNetRoles] ([Id]) ON DELETE CASCADE
go
ALTER TABLE [dbo].[AspNetUserRoles] ADD CONSTRAINT [FK_dbo.AspNetUserRoles_dbo.AspNetUsers_UserId] FOREIGN KEY ([UserId]) REFERENCES [dbo].[AspNetUsers] ([Id]) ON DELETE CASCADE
go
ALTER TABLE [dbo].[AspNetUserClaims] ADD CONSTRAINT [FK_dbo.AspNetUserClaims_dbo.AspNetUsers_UserId] FOREIGN KEY ([UserId]) REFERENCES [dbo].[AspNetUsers] ([Id]) ON DELETE CASCADE
go
ALTER TABLE [dbo].[AspNetUserLogins] ADD CONSTRAINT [FK_dbo.AspNetUserLogins_dbo.AspNetUsers_UserId] FOREIGN KEY ([UserId]) REFERENCES [dbo].[AspNetUsers] ([Id]) ON DELETE CASCADE
go


The code for this series can also be found here: https://github.com/aaronhoffman/AspNetMvc5Identity


Hope this helps,
Aaron