Tags » SQL Query

Find all Foreign Key and Related Tables

SELECT *
/*f.name,OBJECT_NAME(f.parent_object_id) TableName,
COL_NAME(fc.parent_object_id,fc.parent_column_id) ColName*/
FROM
sys.foreign_keys AS f
INNER JOIN
sys.foreign_key_columns AS fc
ON f.OBJECT_ID = fc.constraint_object_id
INNER JOIN
sys.tables t
ON t.OBJECT_ID = fc.referenced_object_id… 128 more words

SQL Query

QUERY TO RUN BACKUP,RESTORE,DELTE USER & ADD USERS,ROLES

DECLARE @db NVARCHAR(100)=’2011120_1030’DECLARE @db NVARCHAR(100)=’2011120_1030’DECLARE @file NVARCHAR(MAX)= N’D:\DB_Bups\AWPROD_1_’+@dt+’.bak’DECLARE @db NVARCHAR(max)=’EVAL’declare @user nvarchar(max)=’navadmin’DECLARE @sql NVARCHAR(max)/*RESTORE FILELISTONLYFROM DISK = @file*/
BACKUP DATABASE TO  DISK = @file WITH NOFORMAT, NOINIT, NAME = N’AWPROD_1-Full Database Backup’, SKIP, NOREWIND, NOUNLOAD,  STATS = 10… 128 more words

SQL Query

Check all SP(stored Procedures)

DECLARE @Name nvarchar(1000);
DECLARE @Sql nvarchar(1000);
DECLARE @Result int;

DECLARE ObjectCursor CURSOR FAST_FORWARD FOR
SELECT QUOTENAME(SCHEMA_NAME(o.schema_id)) + ‘.’ + QUOTENAME(OBJECT_NAME(o.object_id))
FROM sys.objects o
WHERE type_desc IN ( 102 more words

SQL Query

Find Unique Key Index; Find Foriegn Key Index; Find All Key Index

Find Unique Key Index:Find Unique Key Index:—————————-
with connect(schema_name,table_name,index_name,index_column_id,column_name,is_primary_key) as(   select s.name schema_name, t.name table_name, i.name index_name, index_column_id, cast(c.name as varchar(max)) column_name,i.is_primary_key from sys.tables tinner join sys.schemas s on t.schema_id = s.schema_idinner join sys.indexes i on i.object_id = t.object_idinner join sys.index_columns ic on ic.object_id = t.object_id and ic.index_id=i.index_id        inner join sys.columns c on c.object_id = t.object_id and                ic.column_id = c.column_id                where index_column_id=1union allselect s.name schema_name, t.name table_name, i.name index_name, ic.index_column_id, cast(connect.column_name + ‘,’ + c.name as varchar(max)) column_name,i.is_primary_key from sys.tables tinner join sys.schemas s on t.schema_id = s.schema_idinner join sys.indexes i on i.object_id = t.object_idinner join sys.index_columns ic on ic.object_id = t.object_id and ic.index_id=i.index_id        inner join sys.columns c on c.object_id = t.object_id and                ic.column_id = c.column_id join connect onconnect.index_column_id+1 = ic.index_column_idand connect.schema_name = s.nameand connect.table_name = t.nameand connect.index_name = i.name)insert into ConstraintList(ConstraintName,PKColumn,PKTable,FKTable,ColumnNames,Message,ConstraintType,CreatedBy,CreateDate,UpdatedBy,LastUpdateDate,Status)select Constraint_Name = connect.index_name, PK_Column = connect.column_name, PK_Table = connect.table_name, FK_Table = NULL,FK_Column = connect.column_name,’Cannot insert record due to duplicate key violation’,’UNIQUE’,0,’2017-01-01′,0,’2017-01-01′,0from connect join (select schema_name,table_name,index_name,MAX(index_column_id) index_column_idfrom connect group by schema_name,table_name,index_name) mxon connect.schema_name = mx.schema_nameand connect.table_name = mx.table_nameand connect.index_name = mx.index_nameand connect.index_column_id = mx.index_column_id where is_primary_key=0order by 1,2,3… 407 more words

SQL Query

CASE and IN statement

DECLARE @Status VARCHAR(50)
SET @Status=’published’

SELECT * FROM Products P
WHERE @Status=’published’ and P.Status IN (1,3)
or @Status=’standby’ and P.Status IN (2,5,9,6)
or @Status=’deleted’ and P.Status IN (4,5,8,10) 11 more words

SQL Query

GET TRANSACTION

SELECT
.,
. AS ,
DB_NAME (s_tdt.database_id) AS ,
. AS ,
. AS ,
. AS ,
.text AS ,
. AS
FROM
sys.dm_tran_database_transactions… 157 more words

SQL Query

Get all Parents for particular Child (using WITH & CTE)

CREATE TABLE dbo.MyTable (Sno int, refid int)

INSERT INTO dbo.MyTable VALUES
(17, 8),
(16, 8),
(15, 7),
(14, 7),
(13, 6),
(12, 6),
(11, 5), 71 more words

SQL Query