SQL Server - Get dates between two dates

DECLARE @MinDate DATE = '2014-01-01',

        @MaxDate DATE = '2014-02-06';

SELECT  TOP (DATEDIFF(DAY, @MinDate, @MaxDate) + 1)

        Date = DATEADD(DAY, ROW_NUMBER() OVER(ORDER BY a.object_id) - 1, @MinDate)

FROM    sys.all_objects a

        CROSS JOIN sys.all_objects b;

        

Sql Server Find Percentage Calculation Function

CREATE FUNCTION [dbo].[Percentage]

(

    @Percentage               DECIMAL(4,2)      ,

    @ColumnNameValue    DECIMAL(7,2)     

)

RETURNS DECIMAL(7,2)

BEGIN

    DECLARE

            @Output DECIMAL(7,2)

            SET @Output = (@Percentage/100)*@ColumnNameValue

    RETURN @Output

END

select Percentage(10,1000)

output : 100

How to Shrink Your MSSQL Database Log File / Truncate Transaction Log

1. Log into Microsoft SQL Server Management Studio
2. Right click on your database.
3. Select Properties.
4. Click on Options.
5. Change the recovery model to Simple.
6. Click OK.
7. Right click on the database.
8. Select Tasks > Shrink > Database.
9. Click OK.
10. Right click on the database.
11. Select Properties.
12. Click on Options.
13. Change the recovery model to Full.
14. Click OK.

Retrieve List out Clustered Index or Non Clustered Index on Tables for Entire Database

SELECT COUNT(i.TYPE) NoOfIndex,

[schema_name] = s.name, table_name = o.name

FROM sys.indexes i

INNER JOIN sys.objects o ON i.[object_id] = o.[object_id]

INNER JOIN sys.schemas s ON o.[schema_id] = s.[schema_id]

WHERE o.TYPE IN ('U')

--AND i.TYPE = 1

AND i.TYPE = 2

GROUP BY s.name, o.name

ORDER BY schema_name, table_name

--select * from sys.indexes

select * from sys.objects

How to Find Number of Primarykey and Foreignkey columns in Tables

-- No Foreign key Tables

SELECT * FROM INFORMATION_SCHEMA.TABLES

WHERE TABLE_NAME NOT IN

(

      SELECT OBJECT_NAME(f.parent_object_id) AS TableName

      FROM sys.foreign_keys AS f

      INNER JOIN sys.foreign_key_columns AS fc

      ON f.OBJECT_ID = fc.constraint_object_id

)

AND TABLE_TYPE = 'BASE TABLE'

ORDER BY TABLE_NAME;

--select * from sys.foreign_keys

--select * from sys.foreign_key_columns

select * from sys.objects

-- No primary key Tables

SELECT SCHEMA_NAME(schema_id) AS SchemaName,name AS TableName

FROM sys.tables

WHERE OBJECTPROPERTY(OBJECT_ID,'TableHasPrimaryKey') = 0

ORDER BY SchemaName, TableName;

SELECT SCHEMA_NAME(schema_id) AS SchemaName,name AS TableName

FROM sys.tables

WHERE OBJECTPROPERTY(OBJECT_ID,'TableHasForeignRef') = 0

ORDER BY SchemaName, TableName;

How to Find List of Primary Keys and Foreign Keys in Database in Sql Server

SELECT

DISTINCT

Constraint_Name AS [Constraint],

Table_Schema AS [Schema],

Table_Name AS [TableName]

FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE

GO

SELECT OBJECT_NAME(OBJECT_ID) AS NameofConstraint,

SCHEMA_NAME(schema_id) AS SchemaName,

OBJECT_NAME(parent_object_id) AS TableName,

type_desc AS ConstraintType

FROM sys.objects

WHERE type_desc IN ('FOREIGN_KEY_CONSTRAINT','PRIMARY_KEY_CONSTRAINT')

GO

How Column reference in all Tables in Sql Server

SELECT obj.Name SPName, sc.TEXT SPText

FROM sys.syscomments sc

INNER JOIN sys.objects obj ON sc.Id = obj.OBJECT_ID

WHERE sc.TEXT LIKE '%' + 'Email' + '%'

AND TYPE = 'P'

Related Searches : Query for Table Column reference in all Tables in Sql Server .

How to Find Column reference in all Procedures in Sql Server

SELECT obj.Name SPName, sc.TEXT SPText

FROM sys.syscomments sc

INNER JOIN sys.objects obj ON sc.Id = obj.OBJECT_ID

WHERE sc.TEXT LIKE '%' + 'CreatedBy' + '%'

AND TYPE = 'P'

Related Searches : Find the column reference in stored procedure Sql server.