--1--Get the list of all Logins in SQL Server SELECT name AS Login_Name, type_desc AS Account_Type FROM sys.server_principals WHERE TYPE IN ('U', 'S', 'G') and name not like '%##%' ORDER BY name, type_desc --2--List of all SQL Logins only SELECT name FROM sys.server_principals WHERE TYPE = 'S' and name not like '%##%' --3--Get the list … Continue reading All Logins in sql server

--1-- To check last login time of sql server login SELECT MAX(login_time) AS [Last Login Time], login_name [Login] FROM sys.dm_exec_sessions GROUP BY login_name; --2--To check the last login date/time of a connection SELECT MAX(login_time) AS [Last Login Time], login_name [Login] FROM sys.dm_exec_sessions GROUP BY login_name order by 1 desc --3--To check the number of connections … Continue reading All active users in server

Create Proc dbo.abc as begin select getdate() as 'Today' end SELECT OBJECT_NAME(id) FROM SYSCOMMENTS WHERE [text] LIKE '%Today%' AND OBJECTPROPERTY(id, 'IsProcedure') = 1 GROUP BY OBJECT_NAME(id)

EXEC sp_Msforeachdb 'if exists(select object_name(SC.object_id) ''Table'',len(ST.name) ''TableLength'',SC.Name ''Column'',len(SC.name) ''ColumnLength'' from [?].sys.columns SC inner join [?].sys.tables ST on SC.OBJECT_ID=ST.OBJECT_ID and ST.type=''U'' where len(SC.name)>30 or len(ST.name)>30) select ''?'' as FoundInDb,object_name(SC.object_id) ''Table'',len(ST.name) ''TableLength'',SC.Name ''Column'',len(SC.name) ''ColumnLength'' from sys.columns SC inner join sys.tables ST on SC.OBJECT_ID=ST.OBJECT_ID and ST.type=''U'' where len(SC.name)>30 or len(ST.name)>30' another script EXEC sp_Msforeachdb 'select object_name(SC.object_id) ,len(ST.name) ,SC.Name … Continue reading sp_Msforeachdb is not realiable, it is not fetching details from few other DBs

Script to try for ON UPDATE CASCADE drop table cust,custbase Create table custbase (id int primary key,nm char(10)) insert into custbase values (1,'a') insert into custbase values(2,'b') insert into custbase values(3,'c') Create table cust (custid int references custbase(id) ON UPDATE CASCADE, custname char(10)) insert into cust values (1,'Manish') insert into cust values (1,'Manish1') insert into … Continue reading ON UPDATE CASCADE