Note: There are no guarantees or warranties offered for any of these scripts. They work for me, they may not work for you. There are likely better way of doing things. I didn't write all of these, lots are from MSDN docs, collated for simplicity.

Hint: Hover over the code blocks to show the Copy button.

SELECT OBJECT_SCHEMA_NAME(s.object_id) AS SchemaName,
    OBJECT_NAME(s.object_id) AS TableName,
    SUM(s.user_seeks + s.user_scans + s.user_lookups) AS Reads,
    SUM(s.user_updates) AS Writes
FROM sys.dm_db_index_usage_stats AS s
WHERE objectproperty(s.object_id,'IsUserTable') = 1
    AND s.database_id = db_ID()
GROUP BY OBJECT_SCHEMA_NAME(s.object_id), OBJECT_NAME(s.object_id)
ORDER BY Writes DESC, Reads DESC

 

 

SELECT [name], create_date, modify_date
FROM sys.objects
WHERE type = 'P'  
ORDER BY 3 DESC;

 

 

DECLARE @Database NVARCHAR(255)   
DECLARE @Table NVARCHAR(255)  
DECLARE @cmd NVARCHAR(1000)  

DECLARE DatabaseCursor CURSOR READ_ONLY FOR  
SELECT name FROM master.sys.databases   
WHERE name IN ('db1','db2')  -- databases to include
    AND state = 0 -- database is online
    AND is_in_standby = 0 -- database is not read only for log shipping
ORDER BY 1  

OPEN DatabaseCursor  

FETCH NEXT FROM DatabaseCursor INTO @Database  
WHILE @@FETCH_STATUS = 0  
BEGIN  

   SET @cmd = 'DECLARE TableCursor CURSOR READ_ONLY FOR SELECT ''['' + table_catalog + ''].['' + 
   table_schema + ''].['' +   table_name + '']'' as tableName FROM [' + 
    @Database + '].INFORMATION_SCHEMA.TABLES WHERE table_type = ''BASE TABLE'''   

   -- create table cursor  
   EXEC (@cmd)  
   OPEN TableCursor   

   FETCH NEXT FROM TableCursor INTO @Table   
   WHILE @@FETCH_STATUS = 0   
   BEGIN
      BEGIN TRY   
         SET @cmd = 'ALTER INDEX ALL ON ' + @Table + ' REBUILD' 
         --PRINT @cmd -- uncomment if you want to see commands
         EXEC (@cmd) 
      END TRY
      BEGIN CATCH
         PRINT '---'
         PRINT @cmd
         PRINT ERROR_MESSAGE() 
         PRINT '---'
      END CATCH

      FETCH NEXT FROM TableCursor INTO @Table   
   END   

   CLOSE TableCursor   
   DEALLOCATE TableCursor  

   FETCH NEXT FROM DatabaseCursor INTO @Database  
END  
CLOSE DatabaseCursor   
DEALLOCATE DatabaseCursor

 

 

EXEC sp_updatestats
SET NOCOUNT  ON
DECLARE  @SQLcommand NVARCHAR(512), @Table      SYSNAME
DECLARE curAllTables CURSOR  FOR
SELECT table_schema + '.' + table_name
	FROM   information_schema.tables
	WHERE  TABLE_TYPE = 'BASE TABLE'
OPEN curAllTables
FETCH NEXT FROM curAllTables INTO @Table
WHILE (@@FETCH_STATUS = 0)
  BEGIN
    PRINT N'UPDATING STATISTICS FOR TABLE: ' + @Table
    SET @SQLcommand = 'UPDATE STATISTICS ' + @Table + ' WITH FULLSCAN'
    EXEC sp_executesql @SQLcommand
    FETCH NEXT FROM curAllTables
    INTO @Table
  END
CLOSE curAllTables
DEALLOCATE curAllTables
SET NOCOUNT  OFF
GO

 

 

(dateadd(day,(7),dateadd(hour,datediff(hour,(0),getutcdate())+(1),(0))))

 

 

SELECT distinct RIGHT(email, LEN(email) - CHARINDEX('@', email)) as domain 
FROM dbo.tblUsers
WHERE LEN(email) > 0

 

 

ALTER DATABASE [dbname] SET QUERY_STORE CLEAR;

 

 

declare @RowNum int
declare CustList cursor for
select top 5 CustomerID from Northwind.dbo.Customers
OPEN CustList
FETCH NEXT FROM CustList INTO @CustId
set @RowNum = 0 
WHILE @@FETCH_STATUS = 0
BEGIN
  set @RowNum = @RowNum + 1
  print cast(@RowNum as char(1)) + ' ' + @CustId
  FETCH NEXT FROM CustList INTO @CustId
END
CLOSE CustList
DEALLOCATE CustList

 

 

DECLARE @companies VARCHAR(1000)
SELECT @companies = COALESCE(@companies,'') + company + ';' 
FROM tblAccountsprint @companies

 

 

declare @CustId nchar(5)
declare @RowNum int
declare myRecords cursor for
select top 5 CustomerID from Northwind.dbo.Customers
OPEN myRecords
FETCH NEXT FROM myRecords INTO @CustId
set @RowNum = 0
WHILE @@FETCH_STATUS = 0
BEGIN
    set @RowNum = @RowNum + 1
    print cast(@RowNum as char(1)) + ' ' + @CustId
    FETCH NEXT FROM myRecords INTO @CustId
END
CLOSE myRecords
DEALLOCATE myRecords>

 

CREATE TABLE #RowCountsAndSizes (TableName NVARCHAR(128),rows CHAR(11), 
reserved VARCHAR(18),data VARCHAR(18),index_size VARCHAR(18),  unused VARCHAR(18))

EXEC sp_MSForEachTable 'INSERT INTO #RowCountsAndSizes EXEC sp_spaceused ''?'' '

SELECT TableName,CONVERT(bigint,rows) AS NumberOfRows,
CONVERT(bigint,left(reserved,len(reserved)-3)) AS SizeinKB

FROM #RowCountsAndSizes
ORDER BY NumberOfRows DESC,SizeinKB DESC,TableName

DROP TABLE #RowCountsAndSizes

 

 

CREATE TABLE #RowCountsAndSizes (TableName NVARCHAR(128),rows CHAR(11),
reserved VARCHAR(18),data VARCHAR(18),index_size VARCHAR(18), unused VARCHAR(18))

EXEC sp_MSForEachTable 'INSERT INTO #RowCountsAndSizes EXEC sp_spaceused ''?'' '

SELECT TableName,CONVERT(bigint,rows) AS NumberOfRows,
ROUND(CONVERT(float,left(reserved,len(reserved)-3))/(1024*1024),2) AS SizeinGb
FROM #RowCountsAndSizes
ORDER BY NumberOfRows DESC,SizeinGb DESC,TableName

DROP TABLE #RowCountsAndSizes

 

 

SELECT c.name AS ColName, t.name AS TableName
FROM sys.columns c
JOIN sys.tables t ON c.object_id = t.object_id
WHERE c.name LIKE '%foo%' 
ORDER BY tablename

 

 

SELECT name
FROM sys.tables
WHERE OBJECTPROPERTY(OBJECT_ID,'TableHasIndex')=0

 

 

SELECT OBJECT_NAME(object_id)
ROM sys.sql_modules
WHERE OBJECTPROPERTY(object_id, 'IsProcedure') = 1
    AND definition LIKE '%Foo%'
order by OBJECT_NAME(object_id)

 

 

Create Table ##temp
(
    DatabaseName sysname,
    Name sysname,
    physical_name nvarchar(500),
    size decimal (18,2),
    FreeSpace decimal (18,2)
)   
Exec sp_msforeachdb '
Use [?];
Insert Into ##temp (DatabaseName, Name, physical_name, Size, FreeSpace)
    Select DB_NAME() AS [DatabaseName], Name,  physical_name,
    Cast(Cast(Round(cast(size as decimal) * 8.0/1024.0,2) as decimal(18,2)) as nvarchar) Size,
    Cast(Cast(Round(cast(size as decimal) * 8.0/1024.0,2) as decimal(18,2)) -
        Cast(FILEPROPERTY(name, ''SpaceUsed'') * 8.0/1024.0 as decimal(18,2)) as nvarchar) As FreeSpace
    From sys.database_files'
Select * From ##temp
drop table ##temp

 

 

select name as 'Database Name',database_id as 'Database ID',recovery_model_desc from sys.databases

 

 

select * from msdb..sysjobs sj left join master.sys.syslogins sl on sj.owner_sid = sl.sid

 

 

Replace the userguid below with the one you want.

DECLARE @userguid nvarchar(128)
SET @userguid = 'AED66C1F-3D63-4EE1-AB23-D5D087652DA3'
SELECT * FROM aspnetusers WHERE id=@userguid

 

 

select so.name table_name,sc.name column_name,st.name data_type
from yourdbname.dbo.sysobjects so
    inner join syscolumns sc on (so.id =sc.id)
    inner join systypes st on (st.type = sc.type)        
where so.type = 'U'
    and st.name IN ('DATETIME', 'DATE', 'TIME')
order by so.name

 

 

SELECT name,create_date,modify_date FROM sys.procedures
    WHERE modify_date > dateadd(d,-3,getdate()) order by modify_date desc

SELECT name,create_date,modify_date FROM sys.tables
    WHERE modify_date > dateadd(d,-3,getdate()) order by modify_date desc

 

 

(optional so.name='xxx' to filter to a specific table)

SELECT so.name AS table_name, sc.name AS column_name, sm.text AS default_value 
FROM sys.sysobjects so JOIN sys.syscolumns sc ON sc.id = so.id 
LEFT JOIN sys.syscomments sm ON sm.id = sc.cdefault 
WHERE so.xtype = 'U'AND sm.text IS NOT NULL --AND so.name = @yourtable 
ORDER BY so.[name], sc.colid

 

 

CREATE FUNCTION [dbo].[Split](@String nvarchar(4000), @Delimiter char(1))
RETURNS @Results TABLE (Items nvarchar(4000))
AS
BEGIN
    DECLARE @INDEX INT
    DECLARE @SLICE nvarchar(4000)

    SELECT @INDEX = 1
    WHILE @INDEX !=0
    BEGIN
        -- GET THE INDEX OF THE FIRST OCCURENCE OF THE SPLIT CHARACTER
        SELECT @INDEX = CHARINDEX(@Delimiter,@STRING)
        -- NOW PUSH EVERYTHING TO THE LEFT OF IT INTO THE SLICE VARIABLE
        IF @INDEX !=0
        SELECT @SLICE = LEFT(@STRING,@INDEX - 1)
    ELSE
        SELECT @SLICE = @STRING
        -- PUT THE ITEM INTO THE RESULTS SET
        INSERT INTO @Results(Items) VALUES(@SLICE)
        -- CHOP THE ITEM REMOVED OFF THE MAIN STRING
        SELECT @STRING = RIGHT(@STRING,LEN(@STRING) - @INDEX)
        -- BREAK OUT IF WE ARE DONE
        IF LEN(@STRING) = 0 BREAK
    END
    RETURN
END

 

 

CREATE Function [dbo].[RemoveNumericCharacters](@Temp VarChar(1000))
Returns VarChar(1000)
AS
Begin
    Declare @NumRange as varchar(50) = '%[0-9]%'
    While PatIndex(@NumRange, @Temp) > 0
        Set @Temp = Stuff(@Temp, PatIndex(@NumRange, @Temp), 1, '')
    Return @Temp
End

 

 

SELECT  OBJECT_NAME(IDX.OBJECT_ID) AS Table_Name, 
    IDX.name AS Index_Name, 
    IDXPS.index_type_desc AS Index_Type, 
    IDXPS.avg_fragmentation_in_percent  Fragmentation_Percentage
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) IDXPS 
INNER JOIN sys.indexes IDX  ON IDX.object_id = IDXPS.object_id 
    AND IDX.index_id = IDXPS.index_id 
ORDER BY Fragmentation_Percentage DESC

 

 

declare @t varchar(250)
declare @i varchar(250)
declare CustList cursor for
    
SELECT OBJECT_NAME(IDX.OBJECT_ID) AS Table_Name, IDX.name AS Index_Name 
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) IDXPS 
INNER JOIN sys.indexes IDX  ON IDX.object_id = IDXPS.object_id 
    AND IDX.index_id = IDXPS.index_id 
    AND IDXPS.avg_fragmentation_in_percent>25

OPEN CustList
FETCH NEXT FROM CustList INTO @t, @i
WHILE @@FETCH_STATUS = 0
BEGIN
    print @t + ':' + @i

    exec('ALTER INDEX ' + @i + ' ON ' + @t +' REORGANIZE  ')
    print 'ALTER INDEX ' + @i + ' ON ' + @t +' REORGANIZE '      

    FETCH NEXT FROM CustList INTO @t, @i
END
CLOSE CustList
DEALLOCATE CustList

 

 

select a.name as colname,b.name as typename 
from syscolumns a, systypes b -- GAH!
where a.id = object_id(@viewname) 
and a.xtype=b.xtype 
and b.name <> 'sysname'

 

 

SELECT OBJECT_NAME(S.[OBJECT_ID]) AS [OBJECT NAME], 
    I.[NAME] AS [INDEX NAME], 
    USER_SEEKS, 
    USER_SCANS, 
    USER_LOOKUPS, 
    USER_UPDATES 
FROM   SYS.DM_DB_INDEX_USAGE_STATS AS S 
    INNER JOIN SYS.INDEXES AS I ON I.[OBJECT_ID] = S.[OBJECT_ID] AND I.INDEX_ID = S.INDEX_ID 
WHERE  OBJECTPROPERTY(S.[OBJECT_ID],'IsUserTable') = 1
    AND S.database_id = DB_ID()

 

 

declare @rangeStart int = 1
declare @rangeEnd int = 6
declare @rangeSize int = (@rangeEnd - @rangeStart)+1
select (@rangeStart + (ABS(CHECKSUM(NewId())) % @rangeSize)) r

 

 

SELECT NEWID() AS MyNewID

Then define the function:

CREATE FUNCTION random_range 
(
	@rangeStart int,
	@rangeEnd int
)
RETURNS int
AS
BEGIN
	DECLARE @rangeSize int = (@rangeEnd - @rangeStart) + 1

	DECLARE @result int = (@rangeStart + (ABS(CHECKSUM((SELECT top 1 MyNewID FROM Get_NewID)) % @rangeSize))

	-- Return the result of the function
	RETURN @result

END
GO

 

 

WITH CPU_Per_Db
AS
(SELECT 
 dmpa.DatabaseID
 , DB_Name(dmpa.DatabaseID) AS [Database]
 , SUM(dmqs.total_worker_time) AS CPUTimeAsMS
 FROM sys.dm_exec_query_stats dmqs 
 CROSS APPLY 
 (SELECT 
 CONVERT(INT, value) AS [DatabaseID] 
 FROM sys.dm_exec_plan_attributes(dmqs.plan_handle)
 WHERE attribute = N'dbid') dmpa
 GROUP BY dmpa.DatabaseID)
 
 SELECT 
 [Database] 
 ,[CPUTimeAsMS] 
 ,CAST([CPUTimeAsMS] * 1.0 / SUM([CPUTimeAsMS]) OVER() * 100.0 AS DECIMAL(5, 2)) AS [CPUTimeAs%]
 FROM CPU_Per_Db
 ORDER BY [CPUTimeAsMS] DESC;

 

 

delete FROM tblBookingComment WHERE commentid in (
SELECT commentid FROM (
    SELECT 
        (SELECT top 1 commentid FROM tblBookingComment 
        WHERE addedby=1 and bookingid=comments.bookingid and comment=comments.comment) as protocomment,* 
        FROM tblBookingComment as comments WHERE addedby=1 and 
        (select count(*) from tblBookingComment where addedby=1 
        AND bookingid=comments.bookingid and comment=comments.comment
        group by bookingid, comment) > 1
    and commentid <> (
                            SELECT top 1 commentid 
                            FROM tblBookingComment 
                            WHERE bookingid=comments.bookingid and comment=comments.comment
                           )
    ) as t
)

 

 

SELECT
    session_id,
    start_time, 
    [status],
    command,
    blocking_session_id,
    wait_type,
    wait_time,
    open_transaction_count,
    transaction_id,
    total_elapsed_time,
    Definition = CAST(text AS VARCHAR(MAX))
FROM
    SYS.DM_EXEC_REQUESTS
    CROSS APPLY sys.dm_exec_sql_text(sql_handle) 
WHERE blocking_session_id != 0

 

 

SELECT TABLE_SCHEMA, TABLE_NAME, Stuff(
	(
	SELECT ',' + C.COLUMN_NAME
	FROM INFORMATION_SCHEMA.COLUMNS As C
	WHERE C.TABLE_SCHEMA = T.TABLE_SCHEMA AND C.TABLE_NAME = T.TABLE_NAME
	ORDER BY C.ORDINAL_POSITION
	FOR XML PATH('')
	), 1, 1, '') As Columns
FROM INFORMATION_SCHEMA.TABLES As T
--WHERE T.TABLE_NAME='TableName' -- Optional - set to specific table or subset of tables
ORDER BY T.TABLE_NAME

 

 

SELECT SCHEMA_NAME(schema_id) AS SchemaName,name AS TableName
FROM sys.tables
WHERE OBJECTPROPERTY(OBJECT_ID,'TableHasPrimaryKey') = 0
ORDER BY SchemaName, TableName;

 

 

SELECT  
TABLE_NAME FROM INFORMATION_SCHEMA.TABLES  
WHERE
Table_NAME NOT IN  
(  
SELECT DISTINCT c.TABLE_NAME FROM INFORMATION_SCHEMA.COLUMNS c  
INNER  
JOIN sys.identity_columns ic  
on  
(c.COLUMN_NAME=ic.NAME))  
AND  
TABLE_TYPE ='BASE TABLE' 

 

 

SELECT DISTINCT o.name, o.xtype
FROM syscomments c
INNER JOIN sysobjects o ON c.id=o.id
WHERE o.xtype='P'

 

 

SELECT DISTINCT o.name, o.xtype
FROM syscomments c
INNER JOIN sysobjects o ON c.id=o.id
WHERE c.TEXT LIKE '%tblMyTableName%' AND o.xtype='P'

 

 

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 '%SP_NAme%' 
)
SELECT Table_name FROM stored_procedures
WHERE row = 1

 

 

SELECT DISTINCT
    Constraint_Name AS [Constraint],
    Table_Schema AS [Schema],
    Table_Name AS [TableName] 
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
ORDER BY Table_Name,Constraint_Name
GO

 

 

SELECT
    SUM(qrs.count_executions) * AVG(qrs.avg_logical_io_reads) as est_logical_reads,
    SUM(qrs.count_executions) AS sum_executions,
    AVG(qrs.avg_logical_io_reads) AS avg_avg_logical_io_reads,
    SUM(qsq.count_compiles) AS sum_compiles,
    (SELECT TOP 1 qsqt.query_sql_text FROM sys.query_store_query_text qsqt
        WHERE qsqt.query_text_id = MAX(qsq.query_text_id)) AS query_text,    
    TRY_CONVERT(XML, (SELECT TOP 1 qsp2.query_plan from sys.query_store_plan qsp2
        WHERE qsp2.query_id=qsq.query_id
        ORDER BY qsp2.plan_id DESC)) AS query_plan,
    qsq.query_id,
    qsq.query_hash
FROM sys.query_store_query qsq
JOIN sys.query_store_plan qsp on qsq.query_id=qsp.query_id
CROSS APPLY (SELECT TRY_CONVERT(XML, qsp.query_plan) AS query_plan_xml) AS qpx
JOIN sys.query_store_runtime_stats qrs on qsp.plan_id = qrs.plan_id
JOIN sys.query_store_runtime_stats_interval qsrsi on qrs.runtime_stats_interval_id=qsrsi.runtime_stats_interval_id
WHERE    
    qsp.query_plan like N'%%'
    and qsrsi.start_time >= DATEADD(HH, -24, SYSDATETIME())
GROUP BY qsq.query_id, qsq.query_hash
ORDER BY est_logical_reads DESC
GO

 

 

 

 

---