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.
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';
select COLUMN_NAME,DATA_TYPE from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME='TableName';
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%'
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'
In this query we will learn about How to get names of all views in Sql Server.
SELECT * FROM sys.views
In this query we will learn about How to get names of all stored procedure in Sql Server.
SELECT * FROM sys.procedures
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
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'
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';
In this query we will learn about How to get list of all database names in Sql Server.
SELECT Name FROM dbo.sysdatabases Or EXEC sp_databases
In this query we will learn about How to Check default Language of SQL Server.
select @@language AS DefaultLanguage
In this query we will learn about How to Check SQL Server instance name.
select @@SERVERNAME AS ServerName
In this query we will learn about How to add new column to existing SQL Server table.
ALTER TABLE {TABLENAME} ADD {COLUMNNAME} {TYPE}
ALTER TABLE TableName ADD Email NVARCHAR(50)
In this query we will learn about How to remove column from SQL Server Table.
ALTER TABLE TableName DROP COLUMN Email
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
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;