size and row count of top 100 tables

create table #t(d nvarchar(MAX),t nvarchar(MAX),r int,x nvarchar(100),s nvarchar(100),y nvarchar(100),z nvarchar(100))

declare @s nvarchar(MAX)

set @s=replace('if !~! not in (!master!,!model!,!msdb!,!tempdb!) exec [~].dbo.sp_msforeachtable "insert into #t(t, r,x,s,y,z) exec [~].dbo.sp_spaceused !?!"','!',char(39))

EXEC sp_MSForEachDB @command1=@s, @command2="update #t set d='~' where d is null", @replacechar='~'

select top(100) d as base, t as [table], s as size, r as rows from #t order by Cast(LEFT(s,len(s)-3) as int) desc

drop table #t

date range with recursive cte

WITH

DATE_RANGE as (

      SELECT

            [date]=GETDATE()

       UNION ALL

       SELECT DATEADD(day,1,[date])

       FROM DATE_RANGE

      WHERE DATEADD(day,1,[date])<=DATEADD(day,10,GETDATE())

)

SELECT * FROM DATE_RANGE

 

the Size Of All Tables

SET NOCOUNT ON

DBCC UPDATEUSAGE(0)

-- DB size.

EXEC sp_spaceused

-- Table row counts and sizes.

CREATE TABLE #t
(
    [name] NVARCHAR(128),
    [rows] CHAR(11),
    reserved VARCHAR(18),
    data VARCHAR(18),
    index_size VARCHAR(18),
    unused VARCHAR(18)
)

INSERT #t EXEC sp_msForEachTable 'EXEC sp_spaceused ''?'''

SELECT *
FROM   #t

-- # of rows.

SELECT SUM(CAST([rows] AS int)) AS [rows]
FROM   #t
 
DROP TABLE #t

 

compare identitcal files of folder

:bof

    @echo off
    setlocal

:init
  
    set dirDEST=d:\TEST\NEW\
    set dirSOURCE=d:\TEST\OLD\

    if not exist "%dirDEST%" echo dirDEST not found & goto :EOF
    if not exist "%dirSOURCE%" echo dirSOURCE not found & goto :EOF

    for /f "delims=" %%a in ('dir /b /a-d "%dirDEST%" 2^>NUL') do if not exist "%dirSOURCE%%%a" echo %%a does not exist in "dirSOURCE"  
    for /f "delims=" %%a in ('dir /b /a-d "%dirSOURCE%" 2^>NUL') do if not exist "%dirDEST%%%a" echo %%a does not exist in "dirDEST"

 
:eof

Ad hoc update to system catalogs is not supported

EXEC sp_configure 'show advanced options', 1;

RECONFIGURE WITH OVERRIDE

EXEC sp_configure 'xp_cmdshell', 1

RECONFIGURE WITH OVERRIDE

EXEC sp_configure 'Ole Automation Procedures', 1;

RECONFIGURE WITH OVERRIDE

Windows Mobile Compact Framework- Executing Path/Directory

 

System.IO.Path.GetDirectoryName(System.Reflection.Assembly.GetExecutingAssembly().FullName.CodeBase)

compact framework does not support

AppDomain.CurrentDomain.BaseDirectory

System.Environment.CurrentDirectory

Application.StartupPath

EXECUTE permission was denied on the object

exec sp_grantdbaccess N'www1-w(username)', 'DBNAME'

GRANT EXECUTE ON objectname  TO public

SELECT * FROM master.sys.database_permissions AS dp

    JOIN sys.system_objects AS so

    ON dp.major_id = so.object_id

    WHERE dp.class = 1 AND so.parent_object_id = 0 ;

Find Running Queries

 

SELECT      sqltext.TEXT,

            req.session_id,

            req.status,

            req.command,

            req.cpu_time,

            req.total_elapsed_time

                  FROM sys.dm_exec_requests req

                  CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sqltext

KILL [session_id]

SQL SERVER NAME

SELECT @@SERVERNAME

Select * from sysservers where srvid = 0

Select Host_Name() as Computer_Name

EXEC xp_getNetName

SELECT CONVERT(sysname, SERVERPROPERTY(N'servername'))

ALL TABLES Row size and count

DECLARE @temp  as TABLE (

      NAME VARCHAR(100) ,

      ROWS INT,

      RESERVED VARCHAR(100),

      DATA VARCHAR(100),

      INDEX_SIZE VARCHAR(100),

      UNUSED VARCHAR(100))

 

INSERT INTO @temp EXEC sp_msforEachTable " exec sp_spaceused '?' "

 

SELECT * FROM @temp