$files = Get-ChildItem -Path . -Filter "*.sql"$counter = 5foreach ($file in $files) {$newName = "{0:D2}-$file" -f $counterRename-Item -Path $file.FullName -NewName $newName$counter++} Original sql files in a folder After execution of above script, files are renamed.
First step in Python
Python One of my bucket list of this year, though delayed by almost 8 month, finally my first dive in Python, here it is all about. Python is the most powerful language where you can do anything and everything , let us first check if you already have Python installed in your system. Open command … Continue reading First step in Python
All Logins in sql server
--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
All active users in 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
Search a text in stored procedures
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)
sp_Msforeachdb is not realiable, it is not fetching details from few other DBs
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
ON UPDATE CASCADE
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
To get number of records per minutes, means gouping on minutes in a given timeframe.
Some time while reviewing a log file, especially any error log of something like that, we need to find you that how much is error rate per minute, the following piece of code is used for retrieving information for every minute. Let's create an Error log table. CREATE TABLE Errorlog ( logid INT identity(1, 1) … Continue reading To get number of records per minutes, means gouping on minutes in a given timeframe.
Which table is having maximum number of colums
The following script can be used to fetch the tables having maximum number of columns in sql server tables. SELECT st.NAME 'Table Name' ,count(sc.NAME) AS 'No of columns' FROM sys.columns sc WITH (NOLOCK) INNER JOIN sys.tables st WITH (NOLOCK) ON sc.object_id = st.object_id GROUP BY st.NAME ORDER BY 'No of columns' DESC Thanks Manish … Continue reading Which table is having maximum number of colums
How to execute TSQL using Power shell
Being a DBA many times it happens that we need to execute few queries in SQL Server, the following is one of the way to execute SQL Script in PowerShell. 1. Copy this entire script into a PS file ( say C:\Test\sample.ps1) 2. Please edit you Server,DB, and query details 3. Open power shell window … Continue reading How to execute TSQL using Power shell
Details of Primary Keys, Foreign Keys, Unique Keys and Indexes.
The following script can be use to get the list of all Primary Key, Foreign Key and Unique Key. The following script can be used to get the list of all Indexes. Thanks Manish
Data Dictionary
The following script is used to create Data Dictionary for all tables. However these above script can be modified to get details of any specific schema. Thanks Manish
Stored procedures(SP) Vs Functions(UDF)
Stored Procedures Functions Use Used to read and modify data. Used only to read data Execution Execute or Exec is used to execute SP Can used with SELECT statement, JOINS & APPLY (CROSS & OUTER). Joins Cannot JOIN a SP in a SELECT statement. Can JOIN a UDF in a SELECT statement. Temp objects Can … Continue reading Stored procedures(SP) Vs Functions(UDF)
Check Constraint in existing data.
Few days back I was trying to add a check constraint in a table, but the condition was such like that I do not have to worry about existing data, what I need to do was to restrict the new data with the valid constraints. Let's create a table and insert few records. Add a … Continue reading Check Constraint in existing data.
Results as comma separated
Many times we needed to have some values as comma separated , the following are few scenarios. Single Table Following shall be output Having two table and needed joins Following shall be output The following is other way of doing without STUFF function and with Substring. Data selected from a single table (Table2) … Continue reading Results as comma separated
Delete duplicate records
The following are the ways to delete duplicate rows from a table, and its purely depends upon your environment, its recommend to first try it on development server and then move ahead to production environment. Using group by clause and move the records to another table, truncate original and move records back to original. There … Continue reading Delete duplicate records
Find number of tables used in stored procedure.
While doing analysis or reviewing database objects often we used to look and think for objects which are being used in any specific stored procedure or stored procedures. Following is the small script which is used to find out all the table used in stored procedures. -- Query to get list of all table used … Continue reading Find number of tables used in stored procedure.
Count number of rows in stored procedure
Following code is used to count number of rows in stored procedure.
Performance tuning questions in SQL Server.
What are the various tools you used for Sql Server Query optimization/Performance tuning Explain how do you optimize Server for better performance Explain how do you optimize Database for better performance ( Explain in details about, what all we can do with TempDB for better performance) Explain how do you optimize Query for better performance … Continue reading Performance tuning questions in SQL Server.
SQL Diary & my notes 