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

Kalman Filter Matlab Code

clc

clear all

close all

warning off

error = 2;

Xnp = [1 2 3 4];

Zn = [1.1 1.9 3 4];

si = length(Xnp);

A = zeros(si,si);

P = zeros(si,si);

Q = zeros(si,si);

H = zeros(si,si);

R = zeros(si,si)*error;

I = zeros(si,si);

for i = 1:si

    A(i,i) = 1;

    P(i,i) = 1;

    H(i,i) = 1;

    R(i,i) = 1;

    I(i,i) = 1;

end

Xn = Xnp*A;

Pn = (A*P*A')+Q;

prevXn = [];

while(true)

    Kk = (Pn*H')*(((H*Pn*H')+R)');

    Xn = Xn+((Zn-(Xn*H))*Kk);

    Pn = (I-(Kk*H))*Pn;

    if(isempty(prevXn))

        prevXn = Xn;

    else

        con = sum(abs(Xn-prevXn));

        if(con==0)

            break;

        end

    end

end

FXn = Xn

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.

How to insert bulk data in Oracle

Just use @ symbol and path of the sql.

@"C:\database\importData.sql"

Related Searches : Oracle bulk data insert out of memory error,Import sql out of memory error solve,bulk data insert in Oracle.

How to Create a Procedure with Single Column Output

create or replace PROCEDURE CaseWhenExample(dtType in number,outputResult OUT SYS_REFCURSOR) is

BEGIN

OPEN outputResult FOR

select Name from student

END;

Related Searches : Oracle single column cursor result in Stored Procedure, Make Stored Procedure for single column output.

How to call Procedure in Oracle

var c refcursor;

execute pkg_name.get_user('14232', '15', 'TDWL', 'SA', 1, :c);

print c;

Related Searches : Oracle Procedure call using command , Stored procedure call using cursor in Oracle.

Oracle if table not exist create or Delete

declare

   c int;

begin

   select count(*) into c from user_tables where table_name = upper('table1');

   if c = 1 then

      execute immediate 'drop table table1';

   end if;

   execute immediate 'CREATE table table1 (pkey NUMBER)';

end;