How to rename all .sql files in a folder to have a new running sequence using PowerShell
$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…
All Logins in sql server
–1–Get the list of all Logins in SQL Server –2–List of all SQL Logins only –3–Get the list of all Logins in SQL Server –4–List of all Windows Group Login Accounts only –5–List any specific windows principals or active directory –6–To check the default db of all logins
All active users in server
–1– To check last login time of sql server login –2–To check the last login date/time of a connection –3–To check the number of connections per DB in a server
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…
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…
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)…
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…
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…
Something went wrong. Please refresh the page and/or try again.
Follow My Blog
Get new content delivered directly to your inbox.
SQL Diary & my notes 