Count duplicate values – SQL

SELECT YourColumn, COUNT(*) TotalCount
FROM YourTable
GROUP BY YourColumn
HAVING COUNT(*) > 1
ORDER BY COUNT(*) DESC

Copy Table with 1 line of SQL

This will copy a table structure and its data into a new table:

select * into wdsProductList_20130405 from wdsProductList

This will copy a table definition only (in MySQL) without data:

create table foo like bar;

Find a column by name (SQL Server 2008)

How to find column within a database with searching by name:

SELECT * FROM Information_Schema.Columns c WHERE  c.COLUMN_NAME like 'manu%'

Generating SQL with SQL

I used this script today to add a column ‘FileID’ to all the tables which have the column ‘processStatus’, to generate sql statements which I can run.

select 'ALTER TABLE [' + c.TABLE_NAME + '] ADD FileID int NULL'
 FROM Information_Schema.Columns c
 WHERE c.COLUMN_NAME = 'processedStatus'
/* Generated sql to execute
 ALTER TABLE [productMain] ADD FileID int NULL
 ALTER TABLE [composer] ADD FileID int NULL
 ALTER TABLE [manufacturer] ADD FileID int NULL
 ALTER TABLE [productSupplier] ADD FileID int NULL
 ALTER TABLE [artist] ADD FileID int NULL
 ALTER TABLE [productType] ADD FileID int NULL
 ALTER TABLE [properties] ADD FileID int NULL
 */

LightSwitch build – Database View Permissions Needed

While attempting the build process for a LightSwitch application using the Windows authentication, Visual Studio would hang indefinitely. The application would otherwise build fine when not using authentication and would deploy on an IIS server successfully.

It appears that the developer trying to build the application needs “View Permissions” to the database (not just selected tables either) so that LightSwitch is able to reverse engineer the database during the build process. This is possibly needed to create the correct permissions within the application, since security can be defined down to the smallest element or function with code.

Tell your DBA that its only you (the developer) who needs the view permissions for the build, and that there is no security or data risk.

Hope this helps someone, it took me long enough to find!

Filtering AutoComplete Boxes

In this example I have a search screen listing all the streets in the database with 3 autocomplete boxes for filtering – Continent, Country and City. When the user selects Continent, only those countries and cities within the continent should be listed in the auto complete box. Same for when a country is select, only cities within that country should be available within the city autocomplete box. Focusing just on the filter boxes, here is the quick version:

Returning Records With Children

Here I have a screen for searching Street addresses.  Tables are Continent, Country, City and Street.

The database contains many Countries and Cities which do not have addresses. To make the user experience better I only want to load the autocomplete boxes with Countries and Cities that have addresses.

Selecting Existing Child Records

This is something that wasnt so obvious to me at first. I wanted to associate existing street records with an asset record and managed to do so easily using a Modal Window Picker control. For a given asset I could select a street from the database and associate it, making it a child record. But I couldn’t do this when wanted to associate a country with a continent.

I also noticed that drop-down lists are provided for parent objects, but I cant reverse it so that parent objects can look up child objects.