Victor's Blog

Live as if you were to die tomorrow. Learn as if you were to live forever

NAVIGATION - SEARCH

How to find all indexes & foreign keys in the database

I have two useful SQL queries to share.

The first one is to search all indexes in the database

SELECT 
     TableName = t.name,
     IndexName = ind.name,
     IndexId = ind.index_id,
     ColumnId = ic.index_column_id,
     ColumnName = col.name,
     ind.*,
     ic.*,
     col.* 
FROM 
     sys.indexes ind 
INNER JOIN 
     sys.index_columns ic ON  ind.object_id = ic.object_id and ind.index_id = ic.index_id 
INNER JOIN 
     sys.columns col ON ic.object_id = col.object_id and ic.column_id = col.column_id 
INNER JOIN 
     sys.tables t ON ind.object_id = t.object_id 
WHERE 
     ind.is_primary_key = 0 
     AND ind.is_unique = 0 
     AND ind.is_unique_constraint = 0 
     AND t.is_ms_shipped = 0 
ORDER BY 
     t.name, ind.name, ind.index_id, ic.index_column_id

Then it's the query to search all foreign keys in the database:

SELECT
    K_Table = FK.TABLE_NAME,
    FK_Column = CU.COLUMN_NAME,
    PK_Table = PK.TABLE_NAME,
    PK_Column = PT.COLUMN_NAME,
    Constraint_Name = C.CONSTRAINT_NAME
FROM
    INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK
    ON C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK
    ON C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU
    ON C.CONSTRAINT_NAME = CU.CONSTRAINT_NAME
INNER JOIN (
            SELECT
                i1.TABLE_NAME,
                i2.COLUMN_NAME
            FROM
                INFORMATION_SCHEMA.TABLE_CONSTRAINTS i1
            INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE i2
                ON i1.CONSTRAINT_NAME = i2.CONSTRAINT_NAME
            WHERE
                i1.CONSTRAINT_TYPE = 'PRIMARY KEY'
           ) PT
    ON PT.TABLE_NAME = PK.TABLE_NAME

 

How to format a datetime column in ng-grid?

I have been playing with ngGrid control these days. Today, I want to format a date column in the grid. After a little bit research, I found it was very easy to do. All you need is put this in the gridOption object in your controller.

    $scope.gridOptions = {
      data: 'mydata',
      enableRowSelection: true,
      multiSelect: false,
      enableColumnResize: true,
      columnDefs: [
        { field: 'startDate', displayName: 'Date', cellFilter: 'date:\'yyyy-MM-dd\'' }]
    };

In the example, I use "yyyy-MM-dd" as format. But you can change that to whatever format you like :).

Upgrade Orchard from 1.6 to 1.8.

Orchard website has the instruction on how to upgrade the website. I found quite few problems when I did my upgrade. Here are how I did my upgrade.

1. Because I always use the Orchard source code and I have few custom modules. First thing is adding my custom modules to the orchard project and making sure everything can be compiled successfully.

2. Assign the Administrator role to your current Super User account if you haven't done that before. You will need an account with the Site Owner permission before you update your website with the new release. Without this step you won't be able to access the dashboard.

In case you are discovering this notice too late, here is the manual operation to apply. In your database table Orchard_Framework_ContentItemRecord, on the record with id=1 (the site content item), update the value with this content <Data><SiteSettingsPart SuperUser="admin"/></Data>

3. Backup your site & database. 

4. Delete Dependencies folder in the App_data except sites 

5. Delete all modules in the Modules folder. This step is not in the instruction. But I found if I didn't do that, I will get an error like "014-04-03 13:10:17,247 [27] Orchard.Environment.DefaultOrchardHost - (null) - A tenant could not be started: Default

 (null)

System.TypeLoadException: Could not load type 'Orchard.Comments.Services.ICommentValidator' from assembly 'Orchard.Comments, Version=1.8.0.0, Culture=neutral, PublicKeyToken=null'."

6. Run the build.cmd in the source folder.

7. When the build process finishes, you can find your deployment files in the folder "build\MsDeploy". Update your current website using the latest deployment files.

8. If everything goes smoothly, you can browser your website now. 

9. Log into your admin console, Enable the media library module.

10. Enable upgrade modules. And upgrade your site content using the upgrade module

11. If you find you lost your menus, that probably because links has been lost during the upgrade. You can re-link content to your menu in the navigation.

12. Everything should be finished. If you verify everything, you can disable the upgrade module.