-----------------Get
List of All Databases (DB)--------------------
EXEC sp_helpdb
|
-----------------Get procedure text
----------------------------------
exec sp_helptext @objname = 'BeneficiaryEnrolmentDataStatusSave'
|
---------------- Recompile stored procedure
---------------------
EXEC sp_recompile'BeneficiaryEnrolmentDataStatusSave';
GO
|
----------------Recompile all stored procedure on
table---------
EXEC sp_recompile N'txncustomers';
GO
|
----------------Get all columns of a specific data
type-----------
SELECT OBJECT_NAME(c.OBJECT_ID) as Table_Name, c.name as
Column_Name FROM sys.columns AS c JOIN sys.types AS t ON
c.user_type_id=t.user_type_id WHERE t.name = 'varchar' --G
|
---------------Get all Nullable columns of a
table----------------
SELECT OBJECT_NAME(c.OBJECT_ID) as Table_Name, c.name as
Column_Name FROM sys.columns AS c JOIN sys.types AS t ON
c.user_type_id=t.user_type_id WHERE c.is_nullable=0 AND
OBJECT_NAME(c.OBJECT_ID)='txncustomers'
|
----------------Get All table that doesn’t have primary
key--------------
SELECT name AS Table_Name FROM sys.tables WHERE
OBJECTPROPERTY(OBJECT_ID,'TableHasPrimaryKey') = 0 ORDER BY Table_Name;
|
---------------Get All table that don’t have foreign
key------------------
SELECT name AS Table_Name FROM sys.tables WHERE
OBJECTPROPERTY(OBJECT_ID,'TableHasForeignKey') = 0 ORDER BY Table_Name;
|
----------------Get All table that don’t have identity column------------
SELECT name AS Table_Name FROM sys.tables WHERE
OBJECTPROPERTY(OBJECT_ID,'TableHasIdentity') = 0 ORDER BY Table_Name;
|
-----------------Get First Date of Current
Month------------------------
SELECT CONVERT(VARCHAR(25),DATEADD(DAY,-(DAY(GETDATE()))+1,GETDATE()),105)
First_Date_Current_Month;
|
-----------------Get last date of previous
month------------------------
SELECT
CONVERT(VARCHAR(25),DATEADD(DAY,-(DAY(GETDATE())),GETDATE()),105)
Last_Date_Previous_Month;
|
-----------------Get last date of current
month-------------------------
SELECT CONVERT(VARCHAR(25),DATEADD(DAY,-(DAY(GETDATE())),
DATEADD(MONTH,1,GETDATE())),105) Last_Date_Current_Month;
|
-----------------Get first date of next
month----------------------------
SELECT CONVERT(VARCHAR(25),DATEADD(DAY,-(DAY(GETDATE())),
DATEADD(MONTH,1,GETDATE())+1),105) First_Date_Next_Month;
|
-- ---------------Get all tables that contain a
view----------------------
SELECT * FROM INFORMATION_SCHEMA.VIEW_TABLE_USAGE
|
-----------------Get all columns of the table that using in views---------
SELECT * FROM INFORMATION_SCHEMA.VIEW_COLUMN_USAGE
|
-----------------Get all stored procedure from Database----------------
SELECT DISTINCT o.name, o.xtype FROM syscomments c INNER JOIN
sysobjects o ON c.id=o.id WHERE o.xtype='P'
|
----------------
Get all stored procedure from table---------------------
SELECT DISTINCT o.name, o.xtype FROM syscomments c INNER JOIN
sysobjects o ON c.id=o.id WHERE c.TEXT LIKE '%txncustomers%' AND o.xtype='P'
|
---------------- View dependencies of stored procedure ----------------
;WITH stored_procedures AS ( SELECT oo.name AS table_name,
ROW_NUMBER() OVER(partition by o.name,oo.name ORDER BY o.name,oo.name) AS row
FROM sysdepends d INNER JOIN sysobjects o ON o.id=d.id INNER
JOIN sysobjects oo ON oo.id=d.depid WHERE o.xtype = 'P' AND o.name LIKE
'%BeneficiaryEnrolmentDataStatusSave%' ) SELECT Table_name FROM
stored_procedures WHERE row = 1
|
