Pages - Menu

Friday, June 2, 2017

Get Tables and Views containing Column with Specified Name

Here, I will explain how to get the list of table names which contains specific name. I feel this query is very helpful in real scenarios.

For example, We have one key in our tables and which is named as Unique_Key, now there is requirement to change this column name to Master_Key, so there is two way either we have to check all the tables and views and by one to make sure which table and view is using this column name or we can use the below query to identify the table name and and view name within seconds.

Method 1 (Search Tables): 

SELECT      
sc.name  AS 'ColumnName',
    st.name AS 'TableName'
FROM        
sys.columns sc
JOIN
sys.tables  st   ON sc.object_id = st.object_id
WHERE       
sc.name LIKE '%coulmn_name%'
ORDER BY    
TableName,
ColumnName;


Method 2 (Search Tables & Views):

SELECT      
COLUMN_NAME AS 'ColumnName',
TABLE_NAME AS  'TableName'
FROM        
INFORMATION_SCHEMA.COLUMNS
WHERE       
COLUMN_NAME LIKE '%coulmn_name%'
ORDER BY    
TableName,
ColumnName;


Get Column Names from a Table in SQL Server

Here, I will explain, how to get the list of column names using Table name as input. There are multiple ways to get the column names from a table. You can get the column list with Select statement also, but there we will get data too.

If you want to get only list of columns from a table below are the queries which will work for you.

Option 1:

SELECT 
NAME AS [COLUMN NAME] 
FROM 
SYS.COLUMNS
WHERE 
OBJECT_NAME(OBJECT_ID) = @TABLE_NAME
ORDER BY 
COLUMN_ID

Option 2:

SELECT
    SC.NAME [COLUMN NAME]
FROM
    SYSCOLUMNS SC
    INNER JOIN SYSOBJECTS SO ON
    SO.ID = SC.ID
WHERE

    SO.NAME = @TABLE_NAME

Option 3:

you can get the column list from a table with their datatype information using below query.

SP_HELP @TABLE_NAME;



Tuesday, December 27, 2016

JavaScript Where To

The <script> Tag
JavaScript code must be written between <script> and </script> tags.
example
      <script ...>
           JavaScript Code
     </script>
Note: 
Old JavaScript examples may use a type attribute: <script type="text/javascript">. The type Attribute is not necessary. JavaScript is the default scripting language in HTML.

Where to Write JavaScript in <head> or <body>
you can write or place any number of scripts in an HTML document. 
You can write or place scripts in the <head>, or in the <body> section of an HTML page, or in both.

JavaScript in <head>
In below example, a JavaScript function is written in the <head> section of an HTML page.
This function will be called when a button is clicked.

example

<!DOCTYPE html>
<html>
<head>
<script>
function myFirstFunction() {    document.getElementById("demo").innerHTML = "New Paragraph.";
}
</script>
</head>
<body>
<h1>A Web Page</h1>
<p id="demo">A Paragraph</p>
<button type="button" onclick="myFirstFunction()">Test it</button>
</body>
</html>

JavaScript in <body>

In below example, a JavaScript function is written in the <body> section of an HTML page.
This function will be called when a button is clicked.

example

<!DOCTYPE html>
<html>
<body>
<h1>A Web Page</h1>
<p id="demo">A Paragraph</p>
<button type="button" onclick="myFirstFunction()">Test it</button>
<script>
function myFirstFunction() {
   document.getElementById("demo").innerHTML = "New Paragraph.";
}
</script>
</body>
</html>

External JavaScript

You can also write or place the scripts in external files.

External file: myFirstScript.js
function myFirstFunction() {         document.getElementById("demo").innerHTML = "New Paragraph.";
      }

It's better to use the external JavaScript when the same code is used in many web pages. 
To use the external script, mention the name of the script file in the src attribute of a <script> tag.
JavaScript files have the .js extension.
You can place an external script reference in <head> or <body> or both.
example

<!DOCTYPE html>
<html>
<body>
<script src="myFirstScript.js"></script>
</body>
</html>

External References

External scripts can be referenced with full URL or with a physical path relative to the current webpage.

example 1
This below example uses a full URL to link with a external script
<script src="http://www.techspeculum.com/myFirstScript.js"></script>

example 2
This example uses a file with physical path which is located in the specified folder on the current website.
<script src="/javascript/myFirstScript.js"></script>

Advantages of External JavaScript

Below are the advantages of placing scripts in external files.

  • It allows to separate HTML and code.
  • It Can speedup page loads through cached JavaScript files.
  • It helps to read and maintain the HTML and JavaScript easily.


JavaScript Introduction

What is JavaScript?
JavaScript is a dynamic computer programming language with object-oriented capabilities. While, generally speaking, CSS is for presentation, HTML is for content and JavaScript is for interactivity.
JavaScript is lightweight and most commonly used in Web Applications to develop web pages. It allows client side script to interact with user and provide dynamic pages.

Initially, JavaScript designed for adding small amounts of interactivity to a page like hovers and animations and many more. Now, we can use use JavaScript for almost anything up to large applications and games, and can even be used in servers.

Advantages of JavaScript:

1. JavaScript is executed on the client side
This means the code is executed on the User's Processor instead of the Web Server. This saves server traffic, means less load on web server.

2. Immediate Feedback to the end users
Since Code is executed on the User's Computer, results and processing is completed almost instantly depending on the task.
They don't have to wait for a page reload to see if they have forgotten to enter something, as it doesn't need to processed in the web server and sent back response to user.

3. Increased Interactivity
You can develop the interfaces which react when the user hovers over them with a mouse or activates then using keyboard.

4. Extended functionality to web pages
You can create rich interfaces using JavaScript which include items as drag-and-drop components,sliders etc.
You can use third party add-ons like Greasemonkey or can write it by yourself to enable certain features in your web pages.

Disadvantages of JavaScript:

1. Security Issues
JavaScript Codes once appended into the webpages executes on client servers immediately, so it can also be used to exploit the user's system. While certain restrictions is set by modern web standards on web browsers, but still malicious code be executed complying with the restriction set.

2. JavaScript Rendering Varies
Different browser may render JavaScript differently resulting in inconsistency in terms of functionality and interface. while the latest versions of JavaScript and rendering have been geared towards a universal standards, but certain variations still exists.

Thursday, March 3, 2016

Convert Column values to Comma separated one row value

It is very common task to create comma separated values (CSV) from table column. While doing some development, we often need to do this. I will explain this with a very easy example.

Example:

 Assume we are having a table with the following structure and data.

how to Convert Column values to Comma separated one row value

Syntax: 

To Create the above mentioned table and insert data into it.

CREATE TABLE EMPLOYEE_DETAILS (COUNTRY VARCHAR(20), CITY VARCHAR(20), NAME VARCHAR(20))
INSERT INTO EMPLOYEE_DETAILS VALUES ('INDIA','DELHI','AMAN')
INSERT INTO EMPLOYEE_DETAILS VALUES ('INDIA','DELHI','AMAR')
INSERT INTO EMPLOYEE_DETAILS VALUES ('INDIA','DELHI','AKASH')
INSERT INTO EMPLOYEE_DETAILS VALUES ('INDIA','DELHI','ARAVIND')
INSERT INTO EMPLOYEE_DETAILS VALUES ('INDIA','CHENNAI','MAYUR')
INSERT INTO EMPLOYEE_DETAILS VALUES ('INDIA','CHENNAI','MUKESH')
INSERT INTO EMPLOYEE_DETAILS VALUES ('INDIA','CHENNAI','MAYANK')
INSERT INTO EMPLOYEE_DETAILS VALUES ('INDIA','AGRA','RAVI')
INSERT INTO EMPLOYEE_DETAILS VALUES ('INDIA','AGRA','RINKU')


Required Output:

Convert Column values to Comma separated one row value

In the required output we want to have all the names in a single row with comma separated having same country and city.

Syntax :

 To create Comma Separated values in single row.

SELECT DISTINCT
COUNTRY,
CITY,          
SUBSTRING(  
(  
SELECT ','+ED1.NAME  AS [text()]  
FROM EMPLOYEE_DETAILS ED1  
WHERE ED1.COUNTRY = ED2.COUNTRY  AND ED1.CITY = ED2.CITY 
FOR XML PATH ('')  
), 2, 1000
) [NAME]  
FROM EMPLOYEE_DETAILS ED2

Note: 

Text keyword having in red color as background color should be in lower case.