Smart-Thinker - Simple SQL

Friday, March 19, 2010

Home

Products and Modules
       Index
       Events
          Latest Updates
          Events Demo
       UserGroups
          Latest Updates
          UserGroups Demo
       UserProfile
          Latest Updates
          User Profile Demo
       Wall
       Referrals
          Latest Updates
          Referrals Demo

Free Downloads

Resources
       Simple SQL
       Links

Purchase

DNN Blog

Support
       Please Read
       FAQ
       Forums
       Licensing
       Feature Requests

Company
       Contact Us
       About Smart-Thinker

Resources

Simple SQL

Copy freely. Please let us know if you have more or see room for improvement, or if you have a SQL request. Use at own risk

Find all Table Sizes

BEGIN try
DECLARE @table_name VARCHAR(500) ;
DECLARE @schema_name VARCHAR(500) ;
DECLARE @tab1 TABLE(
        tablename VARCHAR (500) collate database_default
,       schemaname VARCHAR(500) collate database_default
);
DECLARE @temp_table TABLE (
        tablename sysname
,       row_count INT
,       reserved VARCHAR(50) collate database_default
,       data VARCHAR(50) collate database_default
,       index_size VARCHAR(50) collate database_default
,       unused VARCHAR(50) collate database_default
);

INSERT INTO @tab1
SELECT t1.name
,       t2.name
FROM sys.tables t1
INNER JOIN sys.schemas t2 ON ( t1.schema_id = t2.schema_id );

DECLARE c1 CURSOR FOR
SELECT t2.name + '.' + t1.name
FROM sys.tables t1
INNER JOIN sys.schemas t2 ON ( t1.schema_id = t2.schema_id );

OPEN c1;
FETCH NEXT FROM c1 INTO @table_name;
WHILE @@FETCH_STATUS = 0
BEGIN
        SET @table_name = REPLACE(@table_name, '[','');
    &am read more ...

How to Rest DNN Host Password

Declare @UserName NVarChar(255)
Declare @NewPassword NVarChar(255)
Declare @PasswordSalt NVarChar(128)
Declare @Application NVarChar(255)

-- Enter the user name and new password between ''
-- do not leave any spaces unless intended to do so.
-- Edit only between single quote characters
Set @UserName = 'host' -- This default DNN host user
Set @NewPassword = 'newpassword' --New password
-- Do modify any code below this line

Set @Application = (SELECT [ApplicationID] FROM aspnet_Users WHERE UserName=@UserName)
Set @PasswordSalt = (SELECT PasswordSalt FROM aspnet_Membership WHERE UserID IN (SELECT UserID FROM aspnet_Users WHERE UserName=@UserName))

Exec dbo.aspnet_Membership_ResetPassword @Application, @UserName, @NewPassword, 10, 10, @PasswordSalt, -5

Number of times a module is used across all portals

SELECT ModuleName, 'Instances' = count(*)
FROM DesktopModules
INNER JOIN ModuleDefinitions ON DesktopModules.DesktopModuleID = ModuleDefinitions.DesktopModuleID
LEFT OUTER JOIN Modules ON ModuleDefinitions.ModuleDefID = Modules.ModuleDefID
GROUP BY ModuleName
ORDER BY 'Instances'

New Users per month including average per day over month

SELECT DATENAME(m , CreatedDate) as Month, YEAR(CreatedDate) as 'Year', COUNT(*) as 'New Users', COUNT(*)/(SELECT day(dateadd(mm,datediff(mm,-1,CAST(MONTH(CreatedDate) AS NVARCHAR) + '/' + '01' + '/' + CAST(YEAR(CreatedDate) AS NVARCHAR)),-1))) as 'Ave Users /day'FROM Users U
INNER JOIN UserPortals UP ON UP.UserID = U.UserID
WHERE PortalID = 6
AND Authorised = 1
GROUP BY DATENAME(m , CreatedDate), MONTH(CreatedDate), YEAR(CreatedDate)

New Users per day

SELECT DAY(CreatedDate) as 'Day', DATENAME(m , CreatedDate) as Month, YEAR(CreatedDate) as 'Year', COUNT(*) As 'New Users'
FROM Users U
INNER JOIN UserPortals UP ON UP.UserID = U.UserID
WHERE PortalID = 6
AND Authorised = 1
GROUP BY DAY(CreatedDate), DATENAME(m , CreatedDate), MONTH(CreatedDate), YEAR(CreatedDate)

Show all Affiliate incoming clicks by Vendor

SELECT VendorName, Clicks
FROM Affiliates A
INNER JOIN Vendors V ON A.VendorId = V.VendorId
WHERE V.Authorized = 1
ORDER BY V.VendorName

Various User Reports

Here are some great queries from the Venexus DotNetNuke Blog
Definitely worth having a look at for more DNN-related subjects.

Last Activity:
SELECT     Users.Username, Users.FirstName, Users.LastName, aspnet_Users.LastActivityDate
FROM         aspnet_Users INNER JOIN
                      Users ON aspnet_Users.UserName = Users.Username
order by aspnet_Users.LastActivityDate DESC

Last Login:
SELECT   Users.Username, Users.FirstName, Users.LastName, aspnet_Membership.LastLoginDate
FROM         Users INNER JOIN
                      aspnet_Users ON Users.Username = aspnet_Users.UserName INNER JOIN
                      aspnet_Membership ON aspnet_Users.UserId = aspnet_Membership.UserIdread more ...

Querying Member Profile Data

I won't copy Richard's good work, but you can read the article here for more information of extracting data from the Membership Provider tables. Click on the link to see more.

read more ...

Determing Size of Database Objects

Some useful advice if you DNN installation is slow or you want to know if your database is too large.

Check the database size (SELECT * FROM Sysfiles) (less than 30K is still OK)
Check the \Portals\_default\Logs folder for big XML files (> 5MBs)
How many rows does your sitelog have (SELECT Count(*) FROM SiteLog)
Check how many rows you got in ScheduleHistory (SELECT Count(*) FROM ScheduleHistory)
Check how many rows you got in EventLog (SELECT Count(*) FROM EventLog)

You might also want to use exec sp_spaceused <tablename> on specific tables to directly determine their physical sizes, index sizes and row counts as follows

exec sp_spaceused SiteLog
exec sp_spaceused ScheduleHistory
exec sp_spaceused EventLog

Changing a password of a user to one you know

--Get info from user you know
SELECT * FROM aspnet_Users aU, aspnet_Membership aM, Users U
WHERE aU.UserID = aM.UserID
AND U.UserName = aU.UserName
AND (U.UserName = '<UserName of user you know>' or U.UserName = '<UserName of user you want to change>')

--Verify
SELECT * FROM aspnet_Membership
where userID = '<UserID from first query>'

--Update
Update aspnet_Membership
Set PasswordSalt = '<PS from UserName you know>',
Password = '<P from UserName you know>'
where userID = '<UserID from first query>' read more ...

Bulk Deletion of Users based on query

Problem: Corresponding user records stored in aspnet membership tables, keyed on lowered username and not userID.
Brief: Need to call aspnet_Users_DeleteUser and DeleteUser (http://forums.asp.net/970280/ShowPost.aspx)

For example, to delete all users with a userID greater than 470:

SELECT UserID FROM Users WHERE UserID > 470

DELETE
FROM dbo.aspnet_Membership
WHERE UserId IN
(
SELECT u.UserId
FROM    dbo.aspnet_Users u
INNER     JOIN dbo.aspnet_Applications a ON u.ApplicationId = a.ApplicationId
INNER     JOIN Users U1 On U1.UserName = u.LoweredUserName
WHERE   LOWER(0) = a.LoweredApplicationName
AND U1.UserID > 470
)

Repeat for tables aspnet_UsersInRoles, aspnetaspnet_Profile, aspnet_Users

and finally
DELETE FROM dbo.Users WHERE UserID > 470

How to link to a page based on a query

SELECT '<a HREF=/default.aspx?tabid=' + CAST(TabID AS VARCHAR) + '>' + T.TabName + '</a>'
FROM Tabs T
WHERE TabName = 'Products and Modules'

Show all Users that belong to Roles about to expire

SELECT DISTINCT(UR.UserID), U.FirstName, U.LastName, R.RoleName, ExpiryDate
FROM USERROLES UR
INNER JOIN USERS U ON UR.UserID = U.UserID
INNER JOIN ROLES R ON UR.RoleID = R.RoleID
WHERE UR.ExpiryDate IS NOT NULL
ORDER BY ExpiryDate

View all Registered Users (Submitted by Leif Leiner)

SELECT U.Username, U.Firstname, U.Lastname, R.Rolename, AM.Email
FROM Users U
INNER JOIN UserRoles UR on U.Userid = UR.Userid
INNER JOIN Roles R on UR.Roleid = R.Roleid
INNER JOIN Aspnet_users AU on U.Username = AU.Username
INNER JOIN Aspnet_membership AM on AU.Userid = AM.Userid
WHERE R.Rolename = 'Registered Users'

Show all banner clickthroughs from active Vendors

SELECT V.VendorName, BannerName, B.Views, B.ClickThroughs, B.Impressions
FROM Banners B
INNER JOIN VENDORS V ON V.VendorID = B.VendorID
WHERE V.Authorized = 1
ORDER BY VendorName, B.ClickThroughs DESC

Find all Table Sizes

How to Rest DNN Host Password

Number of times a module is used across all portals