Use the below query to find all tables containing column with specified name.
SELECT c.name AS ColName, t.name AS TableName
FROM sys.columns c
JOIN sys.tables t ON c.object_id = t.object_id
WHERE c.name LIKE ‘%ColumnName%’
Archive for the ‘SQL Server’ Category
0
SQL Injection Example
In this tutorial on SQL injection, we present a few different examples of SQL injection attacks, along with how those attacks can be prevented. SQL injection attacks typically start with a hacker inputting his or her harmful/malicious code in a specific form field on a website. A website ‘form’, if you don’t already know, is something you have definitely used – like when you log into Facebook you are using a form to login, and a form input field can be any field on a form that asks for your information – whether it’s an email address or a password, these are all form fields.
For our example of SQL injection, we will use a hypothetical form which many people have probably dealt with before: the “email me my password” form, which many websites have in case one of their users forgets their password.
The way a typical “email me my password” form works is this: it takes the email address as an input from the user, and then the application does a search in the database for that email address. If the application does not find anything in the database for that particular email address, then it simply does not send out an email with a new password to anyone. However, if the application does successfully find that email address in its database, then it will send out an email to that email address with a new password, or whatever information is required to reset the password.
But, since we are talking about SQL injection, what would happen if a hacker was not trying to input a valid email address, but instead some harmful SQL code that he wants to run on someone else’s database to steal their information or ruin their data? Well, let’s explore that with an example, starting from how a hacker would typically get started in order to figure out a system works.
Starting the SQL Injection Process
The SQL that would retrieve the email address in the “email me my password” form would typically look something like this – keep in mind that this SQL really is embedded within a scripting language like PHP (it depends on what scripting language is being used by the application):
SELECT data
FROM table
WHERE Emailinput = '$email_input';
This is, of course, a guess at what the SQL being run by the application would look like, because a hacker would not know this information since he does not have access to the application code. The “$email_input” variable is used to hold whatever text the user inputs into the email address form field.
Step 1: Figure out how the application handles bad inputs
Before a hacker can really start taking advantage of a weak or insecure application, he must figure out how the application handles a simple bad input first. Think of this initial step as the hacker “feeling out” his opponent before he releases the really bad SQL.
So, with that in mind, the first step a hacker would typically take is inputting an email address with a quote appended to the end into the email form field. We will of course explain why further down below. But for now, the input from the hacker would look something like this – pay special attention to the fact that there is a quote appended to the end of the email address:
hacker@programmerinterview.com'
If the hacker puts that exact text into the email address form field then there are basically 2 possibilities:
- 1. The application will first “sanitize” the input by removing the extra quote at the end, because we will assume that the application considers email addresses with quotes as potentially malicious. But, a side note: email addresses can actually contain quotes according to IETF standards. Sanitizing data is the act of stripping out any characters that aren’t needed from the data that is supplied – in our case, the email address. Then, the application may run the sanitized input in the database query, and search for that particular email address in the database (without the quote of course).
- 2. The application will not sanitize the input first, and will take the input from the hacker and immediately run it as part of the SQL. This is what the hacker is hoping would happen, and we will assume that this is what our hypothetical application is doing. This is also known as constructing the SQL literally, without sanitizing. What it means is that the SQL being run by the application would look like this – pay extra attention to the fact that there is now an extra quote at the end of the WHERE statement in the SQL below:
SELECT data
FROM table
WHERE Emailinput = 'hacker@programmerinterview.com'';
Now, what would happen if the SQL above is executed by the application? Well, the SQL parser would see that there is an extra quote mark at the end, and it will abort with a syntax error.
The error response is key, and tells the hacker a lot
But, what will the hacker see on the actual form page when he tries to input this email address with a quote at the end? Well, it really depends on how the application is set up to handle errors in the database, but the key here is that the hacker will most likely not receive an error saying something like “This email address is unknown. Please register to create an account” – which is what the hacker would see if the application is actually sanitizing the input. Since we are assuming that the application is not sanitizing it’s input, the hacker would most likely see something like “Internal error” or “Database error” – and now the hacker also knows that the input to the database is not being sanitized . And if the application is not sanitizing it’s input then it means that the database can most probably be exploited, destroyed, and/or manipulated in some way that could be very bad for the application owner.
Step 2: Run the actual SQL injection attack
Now that the hacker now knows the database is vulnerable he can attack further to get some really good information. What could our hacker do? Well, if he’s been able to successfully figure out the layout of the table, he could just type this harmful code on the form field (where the email address would normally go):
Y';
UPDATE table
SET email = 'hacker@ymail.com'
WHERE email = 'joe@ymail.com';
Note that the SQL above is completely SQL compliant and legitimate. You can see that after the Y there is an extra quote followed by a semicolon, which allows the hacker to close the statement and then incredibly run another statement of his own!
Then, if this malicious code is run by the application under attack, it would look like this:
SELECT data
FROM table
WHERE Emailinput = 'Y';
UPDATE table
SET email = 'hacker@ymail.com'
WHERE email = 'joe@ymail.com';
Can you see what this code is doing? Well, it is resetting the email address that belongs to “joe@ymail.com” to “hacker@ymail.com”. This means that the hacker is now changing a user’s account so that it uses his own email address – hacker@ymail.com. This then means that the hacker can reset the password – and have it sent to his own email address! Now, he also has a login and a password to the application, but it is under someone else’s account.
In the example above, we did skip some steps that a hacker would have taken to figure out the table name and the table layout, because we wanted to keep this article relatively short. But, the idea is that SQL injection is a real threat, and taking measures to prevent it is extremely important.
Now, the question is how to prevent SQL injection attacks? Well, read on to the next page or just click here: SQL Injection Prevention.
Script to get the column value with comma separated and vice-versa in SQL Server
Posted: May 30, 2013 in SQL ServerFirst discussed how to concatenate the string with separator:-We can concatenate column value with separator in two different way either using COALESCE or using STUFF XML PATH
E.g. suppose table name is Patient and we want to get all PatientId with comma separated
DECLARE @output varchar(max)
SET @output = null;
SELECT @output = COALESCE(@output + ‘,’,”) + convert(varchar(max),PatientId)
FROM Patient
SELECT @output
Output is
1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18…
Or
SELECT STUFF(
(SELECT ‘,’ + RTRIM(LTRIM(STR(P.PatientId)))
FROM Patient P
ORDER BY P.PatientId
FOR XML PATH(”)),1,1,”) AS CSV
Output is
1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18…
Note:-COALESCE returns the first non null expression among its arguments.
See more details about COALESCE and STUFF here
http://msdn.microsoft.com/en-us/library/ms190349.aspx
http://msdn.microsoft.com/en-us/library/ms188043.aspx
2nd discussed how to split the concatenated string from separator:- Creating split udf just use Charindex and Substring inbuilt sql function and return table value as follows
CREATE FUNCTION [dbo].[Split]
(
@RowData nvarchar(max),
@SplitOn nvarchar(5)
)
RETURNS @RtnValue table
(
Id int identity(1,1),
Data nvarchar(max)
)
AS
BEGIN
Declare @Cnt int
Set @Cnt = 1
While (Charindex(@SplitOn,@RowData)>0)
Begin
Insert Into @RtnValue (data)
Select
Data = ltrim(rtrim(Substring(@RowData,1,Charindex(@SplitOn,@RowData)-1)))
Set @RowData = Substring(@RowData,Charindex(@SplitOn,@RowData)+1,len(@RowData))
Set @Cnt = @Cnt + 1
End
Insert Into @RtnValue (data)
Select Data = ltrim(rtrim(@RowData))
Return
END
E.g. you can call this split in sql query as follows
DECLARE @Str varchar(max)
SET @Str = ‘1,2,3,4,5’
SELECT Data FROM [dbo].[Split](@Str,’,’)
Output:
1
2
3
4
5
Note:- You can call this with table column if that column having value with separator.
|
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
|
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
Here are some more date formats that does not come standard in SQL Server as part of the CONVERT function.
1 To make the month name in upper case, simply use the UPPER string function. |
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
Removes all rows from a table without logging the individual row deletes.
Syntax
TRUNCATE TABLE name
Arguments
name
Is the name of the table to truncate or from which all rows are removed.
Remarks
TRUNCATE TABLE is functionally identical to DELETE statement with no WHERE clause: both remove all rows in the table. But TRUNCATE TABLE is faster and uses fewer system and transaction log resources than DELETE.
The DELETE statement removes rows one at a time and records an entry in the transaction log for each deleted row. TRUNCATE TABLE removes the data by deallocating the data pages used to store the table’s data, and only the page deallocations are recorded in the transaction log.
TRUNCATE TABLE removes all rows from a table, but the table structure and its columns, constraints, indexes and so on remain. The counter used by an identity for new rows is reset to the seed for the column. If you want to retain the identity counter, use DELETE instead. If you want to remove table definition and its data, use the DROP TABLE statement.
You cannot use TRUNCATE TABLE on a table referenced by a FOREIGN KEY constraint; instead, use DELETE statement without a WHERE clause. Because TRUNCATE TABLE is not logged, it cannot activate a trigger.
TRUNCATE TABLE may not be used on tables participating in an indexed view.
Examples
This example removes all data from the authors table.
TRUNCATE TABLE authors
Permissions
TRUNCATE TABLE permissions default to the table owner, members of the sysadmin fixed server role, and the db_owner and db_ddladmin fixed database roles, and are not transferable.
In SQL Server, a view is a pre-written query that is stored on the database. A view consists of a SELECT statement, and when you run the view, you see the results of it like you would when opening a table. Some people like to think of a view as a virtual table. This is because a view can pull together data from multiple tables, as well as aggregate data, and present it as though it is a single table.
Benefits of Views
A view can be useful when there are multiple users with different levels of access, who all need to see portions of the data in the database (but not necessarily all of the data). Views can do the following:
- Restrict access to specific rows in a table
- Restrict access to specific columns in a table
- Join columns from multiple tables and present them as though they are part of a single table
- Present aggregate information (such as the results of the COUNT function)
Accessing Views
Any view that you create ends up being located under the “Views” folder of your database.
The following screenshot shows a number of views that are located within the “AdventureWorks2008” database:
Creating a View
You create a view by using the CREATE VIEW statement, followed by the SELECT statement.
| Code |
|---|
| CREATE VIEW ViewName AS SELECT …  |
Example:
| Code |
|---|
| CREATE VIEW “Alphabetical list of products” AS SELECT Products.*, Categories.CategoryName FROM Categories INNER JOIN Products ON Categories.CategoryID = Products.CategoryID WHERE (((Products.Discontinued)=0)) |
Modifing a View
You can modify an existing view by using using ALTER instead or CREATE.
Example:
| Code |
|---|
| ALTER VIEW “Alphabetical list of products” AS SELECT Products.*, Categories.CategoryName FROM Categories INNER JOIN Products ON Categories.CategoryID = Products.CategoryID WHERE (((Products.Discontinued)=0)) |
You can also right click on the view and select “Design”.
Running a View
You run a view by using a SELECT statement.
| Code |
|---|
| SELECT TOP 1000 * FROM [AdventureWorks2008].[Sales].[vIndividualCustomer] |
You can also right-click on the view and select “Select Top 1000 Rows”.
Running the above view results in this:
As you can see, it looks just like you’ve selected rows from a table. The difference is that, each column could potentially be coming from a different table.
Stored procedures are a powerful part of SQL Server. They can assist programmers and administrators greatly in working with the database configuration and its data.
A stored procedure is a precompiled group of Transact-SQL statements, and is saved to the database (under the “Stored Procedures” node). Programmers and administrators can execute stored procedures either from the SQL Server Management Studio or from within an application as required.
Transact-SQL, which is based on SQL , is the programming language used to interface between applications and their databases. Transact-SQL is a relatively easy language to learn and I highly recommend becoming familiar with it.
Benefits of Stored Procedures
Here are some key benefits in using stored procedures:
| Benefit | Explanation of benefit |
|---|---|
| Modular programming | You can write a stored procedure once, then call it from multiple places in your application. |
| Performance | Stored procedures provide faster code execution and reduce network traffic.
|
| Security | Users can execute a stored procedure without needing to execute any of the statements directly. Therefore, a stored procedure can provide advanced database functionality for users who wouldn’t normally have access to these tasks, but this functionality is made available in a tightly controlled way. |
Creating a Stored Procedure
You create stored procedures in the SQL Server Management Studio using the CREATE PROCEDURE statement, followed by the code that makes up the stored procedure.
| Code |
|---|
| CREATE PROCEDURE StoredProcedureName AS … |
The following code creates a stored procedure called “MyStoredProcedure”:
| Code |
|---|
| CREATE PROCEDURE MyStoredProcedure AS SET ROWCOUNT 10 SELECT Products.ProductName AS TenMostExpensiveProducts, Products.UnitPrice FROM Products ORDER BY Products.UnitPrice DESC |
Once you run this code in the SQL Server Management Studio, the stored procedure is created and appears under the “Stored Procedures” node.
Modifying a Stored Procedure
If you need to modify an existing stored procedure, you simply replace the CREATE with ALTER.
| Code |
|---|
| ALTER PROCEDURE MyStoredProcedure AS … |
Running a Stored Procedure
You can run a stored procedure by using EXECUTE or EXEC. For example, to run the above stored procedure, type the following:
| Code |
|---|
| EXEC MyStoredProcedure |
If the stored procedure has spaces in its name, enclose it between double quotes:
| Code |
|---|
| EXEC “My Stored Procedure” |
If your stored procedure accepts any parameters, they are placed after the procedure name:
| Code |
|---|
| EXEC MyStoredProcedure @ParameterName=”MyParameter” |
So, here’s an example:
| Code |
|---|
| EXEC SalesByCategory @CategoryName =”Beverages” |
Using The GUI
You can also use the graphical user interface to initiate the execution of a stored procedure.
To initiate a stored procedure this way:
- Navigate to the stored procedure
- Right click on the stored procedure and select “Execute Stored Procedure…”:
- A dialog will appear. Enter your chosen parameter values etc:
- Click “OK”
- SQL Server will now generate the SQL code and execute the stored procedure:
Parameters
A parameter is a value that your stored procedure uses in order to perform it’s task. When you write a stored procedure, you can specify the parameters that need to be supplied from the user. For example, if you write a stored procedure to select the address details about an individual, your stored procedure needs to know which individual to select. In this case, the user can provide an IndividualId or UserId to tell the stored procedure which individual to look up.
The PIVOT operator is a useful tool. It lets you aggregate and rotate data so that you can create meaningful tables that are easy to read. However, there are times when you might not want to aggregate data while pivoting a table. For example, you might want to simply pivot the values in Table 1 so that each team has its members in one row, as Table 2 shows.
| Table 1: Original Table |
 |
| Table 2: Pivoted Table |
 |
But as the following basic syntax shows
PIVOT
(Aggregate function (column1)
FOR column2
IN ( [val1], [val2], [val3] )) AS P
where
• column1 is the column you want to aggregate
• column2 is the column you want to pivot
• [val1], [val2], and [val3] are the headings for the pivoted columns
• P is the alias for the results of the PIVOT expression
the PIVOT expression requires an aggregate function.
I’ve developed a solution that lets you pivot data without aggregating it. Listing 1 illustrates this solution using the data in Table 1.
| Listing 1: Code That Pivots a Table Without Aggregating Data |
 |
The SELECT statement in callout B is key to this workaround. In this code, I query the tables’ Team and Member columns as well as the ROW_NUMBER function. I use the OVER clause with this function so that I can partition and order the function’s result set by teams. This groups the members into their respective teams (CRM and ERP) and, within each team, gives members a number that specifies their position in that group (i.e., an ordinal number). Table 3 shows the result set produced by this SELECT statement.
| Table 3: Result Set Produced by the SELECT Statement in Callout B |
 |
Because each ordinal number is associated with only one member in each team, it’s now possible to use the MAX aggregate function in the PIVOT operation. (The maximum value of a data set with only one member will always be that member.) So, in the PIVOT expression in callout C, I use
PIVOT (MAX(Member)
to aggregate the Member column. I want to pivot the RowNum column, which I do with the code
FOR RowNum
In the last segment of the PIVOT expression
IN ([1], [2], [3])) AS pvt
I use aliases for the pivoted column headings. The actual column headings are provided in the SELECT statement in callout A. Note that when a value that will end up as column name doesn’t follow the rules for regular identifiers, you must enclose it in brackets ([ ]). Finally, I assign the PIVOT expression’s results to pvt.
select * from #t
select team,[1] AS TeamMember1 ,[2] AS TeamMember2 ,[3] AS TeamMember3
FROM
(
SELECT team,Member, ROW_NUMBER() OVER (PARTITION By Team ORDER by Team)
AS RowNum
FROM #t
) a
PIVOT (MAX(Member) FOR RowNum IN([1],[2],[3])) AS pvt
What is a Pivot Table in SQL?
What is a Pivot Table in SQL?
In SQL, a pivot table is a set of data that is transformed from a collection of separate rows to a collection of columns. In relational databases, such as Microsoft SQL Server, Oracle and MySQL, pivot tables can be used to simplify extensive data in order to make it easier to read and understand. To create a pivot table, an aggregate is used against a set of data to distribute multiple rows of a single column into a single row with multiple columns. This essentially pivots the result set sideways.
Sample Data
-
To better understand a pivot table, an example of some sales data is listed here. Copy the following into Microsoft SQL Server Management Studio to try out the examples.
Create table #PivotTestTable
(CustName varchar(8),
Item_Type varchar(8),
Item_Amount numeric(6,2))insert into #PivotTestTable
select ‘Jason’, ‘Computer’, 435.34
union
select ‘Jason’, ‘Software’, 243.54
union
select ‘Jason’, ‘Monitor’, 158.23
union
select ‘Alison’, ‘Computer’, 345.89
union
select ‘Alison’, ‘Software’, 78.78
union
select ‘Alison’, ‘Monitor’, 123.45
Starting UnPivoted Data
-
When the temp table, #PivotTestTable, is queried, the result is the following.
CustName Item_Type Item_Amount
——– ——— ———–
Alison Computer 345.89
Alison Monitor 123.45
Alison Software 78.78
Jason Computer 435.34
Jason Monitor 158.23
Jason Software 243.54As you can see, the result set shows two customers, Alison and Jason, who have purchased three different types of items. There are six rows of data for two customers. If we wanted to see the data in a single row per customer, we would use a pivot table to achieve the desired result.
Pivot by PIVOT function
-
Microsoft SQL Server has a PIVOT function built into SQL Server. Here is an example with the #PivotTestTable data.
SELECT
CustName as Total_Sales_By_Cust,
Computer,
Monitor,
Software
FROM
(
SELECT
CustName,
Item_Type,
Item_Amount
FROM #PivotTestTable
) a
PIVOT
(
sum(Item_Amount)
FOR Item_Type in (Computer, Monitor,Software)
) bThis query will return the original six rows pivoted into two rows with separate columns for each type of item sold. The result set generated from this query is here:
Total_Sales_By_Cust Computer Monitor Software
——————- ——— ——– ———
Alison 345.89 123.45 78.78
Jason 435.34 158.23 243.54
Pivot by Aggregated Case Statement
-
By using an aggregate function (SUM, AVG, MIN, MAX) around a case statement in a SQL query, we are able to achieve the same result as the PIVOT function with less work.
SELECT
CustName as Total_Sales_By_Cust,
sum(case Item_Type when ‘Computer’ then Item_Amount end) as Computer,
sum(case Item_Type when ‘Monitor’ then Item_Amount end) as Monitor,
sum(case Item_Type when ‘Software’ then Item_Amount end) as Software
FROM #PivotTestTable
GROUP BY CustNameThis query will return the exact same result set of the previous example and is only a preference for which type of pivot to use.
Common Mistake with Pivot Tables
-
A common mistake to create a pivot table is to create a join back on the source table. This will produce unreliable results and should be avoided. This example is strictly an example of what not to do. The result in this sample will be the same; however this sample will not work in all cases.
SELECT
p1.CustName,
p1.Item_Amount as Computer,
p2.Item_Amount as Monitor,
p3.Item_Amount as Software
FROM #PivotTestTable p1
INNER JOIN #PivotTestTable p2
on p1.CustName = p2.CustName
and p2.Item_Type = ‘Monitor’
INNER JOIN #PivotTestTable p3
on p1.CustName = p3.CustName
and p3.Item_Type = ‘Software’
WHERE p1.Item_Type = ‘Computer’
Below function can be used to remove space between words.
CREATE FUNCTION RemoveAllSpaces
(
@InputStr varchar(8000)
)
RETURNS varchar(8000)
AS
BEGIN
declare @ResultStr varchar(8000)
set @ResultStr = @InputStr
while charindex(‘ ‘, @ResultStr) > 0
set @ResultStr = replace(@InputStr, ‘ ‘, ”)
return @ResultStr
END
Example:
select dbo.RemoveAllSpaces(‘aa aaa aa aa a’)
coldfusion blog 