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.

select COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS

where TABLE_NAME='TableName';

Get column names with data type:

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.

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.

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.

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.

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.

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.

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.

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.

SELECT Name FROM dbo.sysdatabases

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.

select @@language  AS DefaultLanguage

11. Check server name:

In this query we will learn about How to Check SQL Server instance name.

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

ALTER TABLE {TABLENAME} 

ADD {COLUMNNAME} {TYPE}

Usage

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.

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.

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.

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.

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'

Create Web API in Asp.Net Core MVC with Example

Introduction : Here we will learn how to create web api in asp.net core mvc with example or asp.net core mvc rest web api tutorial with example or asp.net core mvc restful api with example or implement web api using asp.net core with examples. By using asp.net core mvc web api templates we can easily implement restful web api services based on our requirements. To create web api first we need to create new project for that Open visual studio à Go to File menu à select New à Project like as shown below Now from web templates select Asp.Net Core Web Application ( .NET Core ) and give name ( CoreWebAPI ) to the project and click OK button like as shown below. Once we click OK button new template will open in that select Web API from Asp.Net Core templates like as shown below Our asp.net core web api project s...

Think Next Generation Technologies

Search This Blog