Have you ever been asked by your colleague anytime to tell you what are the different columns present in a particular sql table, what is the datatype of each column, whether its a nullable or not nullable column so on and so forth ?

Well to go and find out that database table in SSMS and expand the columns node and getting the details should be the last resort. Instead you can make use of INFORMATION_SCHEMA to pull out the details by writing simple query.

INFORMATION_SCHEMA in sql is one of the lesser used methods in sql by developers. It is mostly used by DBA.

Here is a quick tip on how to use in INFORMATION_SCHEMA effectively.

Try running this query in SSMS

Returns all the columns, its datatype, is nullable or not, what is the max length etc 
SELECT *
FROM YOURDATABASE.INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = N’YOURTABLENAME

— Get the name/count of tables present in your database
SELECT *
FROM YOURDATABASE.INFORMATION_SCHEMA.TABLES

SELECT COUNT(*)
FROM YOURDATABASE.INFORMATION_SCHEMA.TABLES

NOTE: For further reading http://msdn.microsoft.com/en-IN/library/ms186778.aspx

Aside  —  Posted: May 13, 2015 in SQL Server
Tags: ,

Running .cmd files using Azure Web Role

Posted: February 13, 2015 in General

Running .cmd file using Azure Web Role was one of the things that I was working on recently which took quite a long time than expected to crack.

This blog post should help some lost soul out there who are trying to google :)

STEP 1: First step is download the latest visual studio updates from the Microsoft site, so that you can use the latest fixes that are released in the update. I downloaded the updates from http://www.microsoft.com/en-us/download/details.aspx?id=44921

STEP 2: Make sure that Azure SDK are installed in your machine.

STEP 3: Create a new Azure Cloud Project as shown in the screen shot below and click OK.

1

STEP 4: In the next screen you will be asked whether you would like to create a web role project along with the cloud service.

2

STEP 5: For this blog post I have created a TestWebRole project and click on Ok.

3

STEP 6: In the next screen select a template,

4

STEP 7: Once you select a template and click ok, the solution explorer should look as the screen shot below.

5

STEP 8: In the TestWebRole project add a Startup.cmd file. And write the code as shown in the screen. This is for creating a MyTest.txt file in the Azure Paas VM.

6

STEP 9: Once we add the cmd file we need to change the properties of the file. Right click on the file and go to properties and change the settings as shown in the screen shot below.

10

11

STEP 10: Go to serviceDefinition.csdef file to add local resource and StartUp tasks as shown.

7

STEP 11: Right click on the azure cloud project and click on package.

8

STEP 12: On click of package you should get the following pop up.

9

STEP 13: on click of package, configuration and package files will be created as shown below.

12

STEP 14: Upload the package to the azure portal and publish the vm. Once the package is deployed if you take a RDP to the vm you should find the MyTest file generated in the server in the following location.

13

Similar to this we can do charms by running .cmd files in azure, like extracting zip file, sending the files from azure to cloud storage etc.

Will be running through this in my next blogs.

Hope this helps someone out there.

Fill handle and Cell Drag and Drop is one of the most commonly used feature or say functionality in excel when dealing with sequences and numbers.

This by far has been the most frequently used functionality in excel throughout my career. Be it writing a formula and applying the same throughout different other cells or writing a common insert/delete script query for SQL and applying the same on other cells in the spreadsheet.

You can enable this feature [Excel 2010] if in case it is not enabled by following steps:

STEP 1: Go to File tab in excel as shown in the screen shot below and click on “Options” below “Help” section

Options

Options

STEP 2: after you click on options in the window that pops up click on “Advanced” section. In this section you can find that there is a check box to “Enable Fill handle and Cell Drag and Drop” as shown below

Advanced Tab

Advanced Tab

STEP 3: Once you enable this write a sequence of number say 1 and 2 in the first two cells. Select the two cells and drag it down till 5th row to see the numbers being filled in a sequence.

Drag Drop

Drag Drop

Well the feature is used very commonly but finding where to enable or disable needs lil bit of “Googling” and this blog serves the purpose :)

Aside  —  Posted: January 16, 2014 in Excel