Skip to main content

SQL Server Tutorial - system queries

In this article we will learn about some of the mostly used SQL Server queries every developer should know. 
These queries can be asked you as an Interview Question or they are handy for you in your day to day tasks.

1. Get all column names from table:

In this query we will learn about How to get all column names from Sql Server table.
1
2
select COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS
where TABLE_NAME='TableName';

Get column names with data type:

1
2
select COLUMN_NAME,DATA_TYPE from INFORMATION_SCHEMA.COLUMNS
where TABLE_NAME='TableName';

2. Search column name from database:

In this query we will learn about How to search any column name from Sql Server database. This query will look out for matching column names in database.
1
2
3
4
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 '%columnNameToSearch%'       

3. Get all user created tables:

In this query we will learn about How to get names of all user created tables in Sql Server.
1
SELECT NAME FROM sys.objects WHERE TYPE='U'  

4. Get all view names:

In this query we will learn about How to get names of all views in Sql Server.
1
SELECT * FROM sys.views  

5. Get all stored procedure names:

In this query we will learn about How to get names of all stored procedure in Sql Server.
1
SELECT * FROM sys.procedures  

6. Counting rows for all tables at once:

In this query we will learn about How to fetch the row count for all tables in a SQL SERVER database. This query is very usefull when we want to know how many records are present in tables.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
SELECT
[TableName] = so.name,
    [RowCount] = MAX(si.rows)
FROM
    sysobjects so,
    sysindexes si
WHERE
    so.xtype = 'U'
    AND
    si.id = OBJECT_ID(so.name)
GROUP BY
    so.name
ORDER BY
    2 DESC       

7. Get Comma Separated List of all columns in table:

In this query we will learn about How to get Comma Separated List of all columns in table.
1
2
3
4
5
6
7
8
9
10
11
12
Select TABLE_SCHEMA, TABLE_NAME
, Stuff(
(
Select ',' + C.COLUMN_NAME
From INFORMATION_SCHEMA.COLUMNS As C
Where C.TABLE_SCHEMA = T.TABLE_SCHEMA
    And C.TABLE_NAME = T.TABLE_NAME
Order By C.ORDINAL_POSITION
For Xml Path('')
), 1, 1, '') As Columns
From INFORMATION_SCHEMA.TABLES As T
WHERE T.TABLE_NAME='TableName'

8. Generate ADO .net parameters:

In this query we will learn about How to Generate ADO .net SqlCommand parameters in Sql Server. Fellows who have worked on asp .net webforms and ADO .net must know the pain of generating parameters for SqlCommand object for large tables. I have written this query to save my time and generate parameters on the fly. This is one of my favourate SQL Query.
1
2
SELECT 'cmd.Parameters.AddWithValue("@'+COLUMN_NAME+'", '+COLUMN_NAME+');'
FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='TableName';  

9. Retrieve List of All Database names:

In this query we will learn about How to get list of all database names in Sql Server.
1
SELECT Name FROM dbo.sysdatabases
OR
1
EXEC sp_databases

10. Check default Language of SQL Server:

In this query we will learn about How to Check default Language of SQL Server.
1
select @@language  AS DefaultLanguage

11. Check server name:

In this query we will learn about How to Check SQL Server instance name.
1
select @@SERVERNAME  AS ServerName

12. Add columns to existing table:

In this query we will learn about How to add new column to existing SQL Server table.

Syntax

1
2
ALTER TABLE {TABLENAME}
ADD {COLUMNNAME} {TYPE}

Usage

1
2
ALTER TABLE TableName
ADD Email NVARCHAR(50)

13. Remove column from a table:

In this query we will learn about How to remove column from SQL Server Table.
1
2
ALTER TABLE TableName
DROP COLUMN Email

14. Check column having NULL values only from a table:

In this query we will learn about How to check if any column contains only NULL values in SQL Server Table.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
DECLARE @col varchar(255), @cmd varchar(max)
DECLARE getinfo cursor for
SELECT c.name FROM sys.tables t JOIN sys.columns c ON t.Object_ID = c.Object_ID
WHERE t.Name = 'TableName'
OPEN getinfo
FETCH NEXT FROM getinfo into @col
WHILE @@FETCH_STATUS = 0
BEGIN
    SELECT @cmd = 'IF NOT EXISTS (SELECT top 1 * FROM TableName WHERE [' + @col + '] IS NOT NULL) BEGIN print ''' + @col + ''' end'
    EXEC(@cmd)
    FETCH NEXT FROM getinfo into @col
END
CLOSE getinfo
DEALLOCATE getinfo

15. Get list of tables without primary key:

In this query we will learn about How to get list of tables without primary key in SQL Server.
1
2
3
4
SELECT SCHEMA_NAME(schema_id) AS SchemaName,name AS TableName
FROM sys.tables
WHERE OBJECTPROPERTY(OBJECT_ID,'TableHasPrimaryKey') = 0
ORDER BY SchemaName, TableName;

16. Get list of tables without having identity column:

In this query we will learn about How to get list of tables without having identity column in SQL Server.
1
2
3
4
5
6
7
8
9
10
11
12
SELECT 
TABLE_NAME FROM INFORMATION_SCHEMA.TABLES 
WHERE
Table_NAME NOT IN 
( 
SELECT DISTINCT c.TABLE_NAME FROM INFORMATION_SCHEMA.COLUMNS c 
INNER 
JOIN sys.identity_columns ic 
on 
(c.COLUMN_NAME=ic.NAME)) 
AND 
TABLE_TYPE ='BASE TABLE' 
Labels:

Comments

Post a Comment

Popular posts from this blog

.NET Core 2.0 Changes – 4 Key Things to Know

1. .NET Standard 2.0 Expanded APIs & the Ability to Reference Full Framework Libraries .NET Standard broadens the set of APIs available to include a lot of the missing features. It now supports 32,000+ APIs. It is now much easier to port your code to a .NET Standard library without major code changes. One of the biggest problems with .NET Core was the lack of third-party libraries. For example, when 1.0 came out, popular logging libraries like log4net were not even available (it is now). However, this was really only a problem if you wanted to deploy your app on Mac or Linux. You could have used .NET Core and targeted full .NET framework and not had these issues. .NET Standard 2.0 has added a  new compatibility shim  that will enable any .NET Core app to reference any full framework library. 2. Expanded OS Support One of the big goals with .NET Core is portability across multiple operating systems. Including desktops, servers, and even mobile. Microsoft ...
Post a Comment
Read more

ASP.NET Core - MVC Design Pattern

The MVC (Model-View-Controller) design pattern is a design pattern that's actually been around for a few decades, and it's been used across many different technologies, everything from Smalltalk to C++ to Java and now in C# and .NET as a design pattern to use when you're building a user interface. The MVC design pattern is a popular design pattern for the user interface layer of a software application. In larger applications, you typically combine a model-view-controller UI layer with other design patterns in the application, like data access patterns and messaging patterns. These will all go together to build the full application stack. The MVC separates the user interface (UI) of an application into the following three parts − The Model  − A set of classes that describes the data you are working with as well as the business logic. The View  − Defines how the application’s UI will be displayed. It is a pure HTML which decides how the UI is going to loo...
Post a Comment
Read more

Become a MEAN Stack Developer

The MEAN stack is  MongoDB ,  Express.js ,  AngularJS  (or  Angular ), and  Node.js . Because all components of the MEAN stack support programs written in JavaScript, MEAN applications can be written in one language for both  server-side  and  client-side  execution environments. MEAN  was coined by Valeri Karpov, a MongoDB developer. He introduced the term in a blog post. The logo concept, initially created by Austin Anderson for the original MEAN stack  LinkedIn  group, is an assembly of the first letter of each component of the MEAN acronym. The components of the MEAN stack are as follows: M ongoDB, a NoSQL database E xpress.js, a web application framework that runs on Node.js A ngular.js or  A ngular, JavaScript MVC frameworks that run in browser JavaScript engines N ode.js, an execution environment for event-driven server-side and networking appl...
Post a Comment
Read more