Posts

Showing posts with the label SQL

SQL Views - Variables

CREATE VIEW VW_X AS WITH MyVars (AnoInit, AnoEnd)   AS ( SELECT   2010 AS 'AnoInit',   (SELECT TOP 1 AnoLetivo FROM TB_Anos) AS 'AnoEnd'   ) SELECT               AnoX,               (SELECT AnoInit  FROM MyVars) as AnoInit,              (SELECT AnoEnd  FROM MyVars) as AnoEnd  FROM TABLE GO

sql - date queries

--Get month names with month numbers ; WITH months ( MonthNumber ) AS (     SELECT 0     UNION ALL     SELECT MonthNumber + 1     FROM months     WHERE MonthNumber < 12 ) SELECT DATENAME ( MONTH , DATEADD ( MONTH ,- MonthNumber , GETDATE ())) AS [MonthName] , Datepart ( MONTH , DATEADD ( MONTH ,- MonthNumber , GETDATE ())) AS MonthNumber FROM months ORDER BY Datepart ( MONTH , DATEADD ( MONTH ,- MonthNumber , GETDATE ())) ; --Get name of day select DATENAME ( WEEKDAY , GETDATE ()) AS TodayIs --Get first date of current month SELECT CONVERT ( VARCHAR ( 25 ), DATEADD ( DAY ,-( DAY ( GETDATE ()))+ 1 , GETDATE ()), 105 ) FirstDate ; --Get last date of current month SELECT CONVERT ( VARCHAR ( 25 ), DATEADD ( DAY ,-( DAY ( GETDATE ())), DATEADD ( MONTH , 1 , GETDATE ())), 105 ) LastDate ; --Get first date of previous month select DATEADD (...

sql - simple queries

--Create table CREATE TABLE TableName (    Id INT   IDENTITY ( 1 , 1 ) PRIMARY KEY ,    Name Nvarchar ( 500 ),    Age INT ) --Create View CREATE VIEW view_name AS SELECT Id , Name , Age FROM TableName --Create Stored procedure CREATE PROCEDURE getInfoFromTable AS SELECT * FROM TableName --Add columns to existing table ALTER TABLE { TABLENAME } ADD { COLUMNNAME } { TYPE } --Remove column from a table ALTER TABLE TableName DROP COLUMN Email --Swap values of 2 columns UPDATE TableName SET Column1 = Column2 , Column2 = Column1 --Get Id of latest inserted record INSERT INTO TableName ( NAME , Email , Age ) VALUES ( 'Test User' , 'test@gmail.com' , 30 ) SELECT SCOPE_IDENTITY () --Delete duplicate records DELETE FROM TableName WHERE ID NOT IN ( SELECT MAX ( ID ) FROM TableName GROUP BY DuplicateColumn ) --Replace a string in a SQL Server ...

sql - advanced queries

--Get all column names from 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' ; --Search column name from 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%'     --Get all user created tables SELECT NAME FROM sys . objects WHERE TYPE = 'U' --Get all view names SELECT * FROM sys . views --Get all stored procedure names SELECT * FROM sys . procedures --Counting rows for all tables at once SELECT [TableName] = so . name ,     [RowCount] = MAX ( si . rows ) FROM     sysobjects so ,     sysindexes si WHERE     so . xtype = '...