Script out database – tables with indexes and primary keys

Posted on 3 martie 2015 by admin

Here’s a piece of code that will help you script out a database, along with it’s tables, primary keys and indexes:

/* CREATE Temp Database */
CREATE DATABASE adventureworks2012_temporary 
ON PRIMARY (NAME = adventureworks2012_temporary, filename = N’C:\AdventureWorks2012_temporary.mdf’, size = 10mb, maxsize = unlimited, filegrowth = 1024kb) 
log ON (NAME = adventureworks2012_temporary_log, filename = N’C:\AdventureWorks2012_temporary.LDF’, size = 1mb, maxsize = 10gb, filegrowth = 5256000kb)

/* GET tables list of the database*/

USE adventureworks2012;
SELECT NAME
INTO   ##tables_list
FROM   sys.tables
WHERE  type = ‘u’

/* Create tables */

–- create temporary tables to hold the columns
CREATE TABLE ##createtable
(
columncreate VARCHAR (500)
)
CREATE TABLE ##createtable_final
(
columncreate_final VARCHAR (500)
)

–- go through each table and script out its columns

USE adventureworks2012;
DECLARE @table VARCHAR(100)
DECLARE @sql VARCHAR(max)

DECLARE tablescreationcursor CURSOR FOR
SELECT NAME
FROM   ##tables_list

OPEN tablescreationcursor
FETCH next
FROM  tablescreationcursor
INTO  @table
WHILE @@FETCH_STATUS = 0
BEGIN

INSERT INTO ##createtable
SELECT ‘  [‘ 
      + column_name + ‘] ‘ 
      + data_type 
      + CASE WHEN character_maximum_length = -1 THEN ‘(MAX)’  
-–if lenght is set to max then character_maximum_length returns -1
        ELSE COALESCE(‘(‘+Cast(character_maximum_length AS VARCHAR)+‘)’, ‘)                END             
      + ‘ ‘  
      + CASE WHEN EXISTS  ( SELECT id 
                            FROM syscolumns 
                            WHERE Object_name(id )=  @table 
                            AND NAME=column_name 
                           AND Columnproperty(id, NAME, ‘isidentity‘ ) = 1 ) 
        THEN ‘IDENTITY(‘ + Cast(Ident_seed(@table) AS VARCHAR)  + ‘,‘ 
             + Cast ( Ident_incr(@table) AS VARCHAR) + ‘)‘  ELSE ‘
        END 
      + ‘ ‘ 
      + (CASE WHEN is_nullable = ‘No’ 
         THEN ‘NOT ‘
         ELSE '' 
         END )  
      + ‘NULL ‘  
      + COALESCE(‘DEFAULT ‘ + column_default, ‘) + ‘,’
FROM information_schema.columns
WHERE table_name = @table

SET @sql = ‘USE AdventureWorks2012_Temporary; CREATE TABLE [‘ + @table + ‘](‘
SELECT @sql = @sql + columncreate
FROM   ##createtable
–- remove trailing comma
SET @sql=LEFT(@sql, len(@sql)-1) + ‘)’ 

INSERT INTO ##createtable_final
SELECT @sql

TRUNCATE TABLE ##createtable

FETCH next
FROM  tablescreationcursor
INTO  @Table
END 
CLOSE tablescreationcursor 
DEALLOCATE tablescreationcursor

/* Create the tables */
DECLARE @RowCount int SET @RowCount =
(
SELECT count (*)
FROM   ##createtable_final) 
DECLARE @Iterator int 
SET @Iterator = 1 
WHILE @Iterator<= @RowCount
BEGIN SET @Command =
(
SELECT TOP 1
columncreate_final
FROM   ##createtable_final) 
EXEC (@command)
DELETE ##createtable_final
WHERE  columncreate_final = @command 
SET @Iterator = @Iterator + 1
END

/* Create primary keys */

DECLARE @TableName varchar(100) 
DECLARE @ConstraintName varchar (100) 
DECLARE @ColumnName varchar (100) 
USE adventureworks2012;
select     i.NAME                              AS constraintname,
object_name(ic.object_id)           AS tablename,
col_name(ic.object_id,ic.column_id) AS columnname
INTO       ##table_primarykeys
FROM       sys.indexes       AS i
INNER JOIN sys.index_columns AS ic
ON         i.object_id = ic.object_id
AND        i.index_id = ic.index_id
WHERE      i.is_primary_key = 1

DECLARE tablespkcursor CURSOR FOR
SELECT constraintname,tablename ,columnname
FROM   ##table_primarykeys
OPEN tablespkcursor
FETCH next
FROM  tablespkcursor
INTO  @ConstraintName,@TableName,@ColumnName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @command = ‘USE AdventureWorks2012_temporary; ALTER TABLE [‘ +
@TableName + ‘] ADD CONSTRAINT [‘ + @ConstraintName
+ ‘] PRIMARY KEY CLUSTERED (‘ + @ColumnName + ‘)’
EXEC (@command)
FETCH next
FROM  tablespkcursor
INTO  @ConstraintName,@TableName ,@ColumnName
END
CLOSE tablespkcursor
DEALLOCATE tablespkcursor

/* Create indexes */

USE adventureworks2012;
select replicate(‘ ‘,4000) AS colnames ,
object_name(i.id)   AS tablename,
i.id                AS tableid,
i.indid             AS indexid,
i.NAME              AS indexname,
i.status,
indexproperty (i.id,i.NAME,‘ISUNIQUE’)        AS isunique,
indexproperty (i.id,i.NAME,‘ISCLUSTERED’)     AS isclustered,
indexproperty (i.id,i.NAME,‘INDEXFILLFACTOR’) AS indexfillfactor
INTO   ##indextablelist
FROM   sysindexes i
WHERE  i.indid > 0
AND    i.indid < 255
AND    (
i.status & 64)=0
AND    i.id>100
AND    object_name(i.id) IN
(
SELECT NAME
FROM   ##tables_list)
DECLARE @ISQL     varchar(4000),
@TABLEID        int,
@INDEXID        int,
@MAXTABLELENGTH int,
@MAXINDEXLENGTH int
–-USED FOR FORMATTING ONLY
SELECT @MAXTABLELENGTH=max(len(tablename))
FROM   ##indextablelist
SELECT @MAXINDEXLENGTH=max(len(indexname))
FROM   ##indextablelist

DECLARE c1 CURSOR FOR
SELECT tableid,
indexid
FROM   ##indextablelist

OPEN c1
FETCH next
FROM  c1
INTO  @TABLEID,
@INDEXID
WHILE @@FETCH_STATUS <> -1
BEGIN

SET @ISQL = ''
SELECT     @ISQL=@ISQL + isnull(syscolumns.NAME,”) + ‘,’
FROM       sysindexes i
INNER JOIN sysindexkeys
ON         i.id=sysindexkeys.id
AND        i.indid=sysindexkeys.indid
INNER JOIN syscolumns
ON         sysindexkeys.id=syscolumns.id
AND        sysindexkeys.colid=syscolumns.colid
WHERE      i.indid > 0
AND        i.indid < 255
AND        (
i.status & 64)=0
AND        i.id=@TABLEID
AND        i.indid=@INDEXID
ORDER BY   syscolumns.colid

UPDATE ##indextablelist
SET    colnames=@ISQL
WHERE  tableid=@TABLEID
AND    indexid=@INDEXID

FETCH next
FROM  c1
INTO  @TABLEID,@INDEXID
END
CLOSE c1
DEALLOCATE c1

-–AT THIS POINT, THE ‘COLNAMES’ COLUMN HAS A TRAILING COMMA
UPDATE ##indextablelist
SET    colnames=LEFT(colnames,len(colnames) -1)
SELECT ‘USE AdventureWorks2012_Temporary; CREATE ‘ +
CASE
WHEN isunique = 1 THEN ‘UNIQUE‘
ELSE ‘‘
END +
CASE
WHEN isclustered = 1 THEN ‘ CLUSTERED ‘
ELSE ‘           ‘
END + ‘ INDEX [‘ + upper(indexname) + ‘]’ + space(@MAXINDEXLENGTH -
len(indexname)) +‘ ON [‘ + upper(tablename) + ‘] ‘ + space(@MAXTABLELENGTH -
len(tablename)) + ‘(‘ + upper(colnames) + ‘)’ +
CASE
WHEN indexfillfactor = 0 THEN ‘ ‘
ELSE ‘ WITH FILLFACTOR = ‘ + CONVERT(varchar(10),indexfillfactor)
END AS indexcreate
INTO   ##indexcreate
FROM   ##indextablelist

/* Create the indexes */

SET @RowCount =(SELECT count (*)FROM   ##indexcreate)
SET @Iterator = 1
WHILE @Iterator <= @RowCount
BEGIN
SET @Command =
(
SELECT TOP 1 indexcreate FROM   ##indexcreate)
EXEC (@command)
DELETE ##indexcreate
WHERE  indexcreate = @command
SET @Iterator = @Iterator + 1
END

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

176

177

178

179

180

181

182

183

184

185

186

187

188

189

190

191

192

193

194

195

196

197

198

199

200

201

202

203

204

205

206

207

208

209

210

211

212

213

214

215

216

217

218

219

220

221

222

223

224

225

226

227

228

229

230

231

232

233

234

235

236

237

238

239

240

241

242

243

244

245

246

247

248

249

250

251

252

/* CREATE Temp Database */

CREATE DATABASE adventureworks2012_temporary

ON PRIMARY (NAME = adventureworks2012_temporary, filename = N’C:\AdventureWorks2012_temporary.mdf’, size = 10mb, maxsize = unlimited, filegrowth = 1024kb)

log ON (NAME = adventureworks2012_temporary_log, filename = N’C:\AdventureWorks2012_temporary.LDF’, size = 1mb, maxsize = 10gb, filegrowth = 5256000kb)

/* GET tables list of the database*/

USE adventureworks2012;

SELECT NAME

INTO ##tables_list

FROM sys.tables

WHERE type = ‘u’

/* Create tables */

–- create temporary tables to hold the columns

CREATE TABLE ##createtable

(

columncreate VARCHAR (500)

)

CREATE TABLE ##createtable_final

(

columncreate_final VARCHAR (500)

)

–- go through each table and script out its columns

USE adventureworks2012;

DECLARE @table VARCHAR(100)

DECLARE @sql VARCHAR(max)

DECLARE tablescreationcursor CURSOR FOR

SELECT NAME

FROM ##tables_list

OPEN tablescreationcursor

FETCH next

FROM tablescreationcursor

INTO @table

WHILE @@FETCH_STATUS = 0

BEGIN

INSERT INTO ##createtable

SELECT ‘ [‘

+ column_name + ‘] ‘

+ data_type

+ CASE WHEN character_maximum_length = -1 THEN ‘(MAX)’

-–if lenght is set to max then character_maximum_length returns -1

ELSE COALESCE(‘(‘+Cast(character_maximum_length AS VARCHAR)+‘)’, ‘) END

+ ‘ ‘

+ CASE WHEN EXISTS ( SELECT id

FROM syscolumns

WHERE Object_name(id )= @table

AND NAME=column_name

AND Columnproperty(id, NAME, ‘isidentity‘ ) = 1 )

THEN ‘IDENTITY(‘ + Cast(Ident_seed(@table) AS VARCHAR) + ‘,‘

+ Cast ( Ident_incr(@table) AS VARCHAR) + ‘)‘ ELSE ‘

END

+ ‘ ‘

+ (CASE WHEN is_nullable = ‘No’

THEN ‘NOT ‘

ELSE ''

END )

+ ‘NULL ‘

+ COALESCE(‘DEFAULT ‘ + column_default, ‘) + ‘,’

FROM information_schema.columns

WHERE table_name = @table

SET @sql = ‘USE AdventureWorks2012_Temporary; CREATE TABLE [‘ + @table + ‘](‘

SELECT @sql = @sql + columncreate

FROM ##createtable

–- remove trailing comma

SET @sql=LEFT(@sql, len(@sql)-1) + ‘)’

INSERT INTO ##createtable_final

SELECT @sql

TRUNCATE TABLE ##createtable

FETCH next

FROM tablescreationcursor

INTO @Table

END

CLOSE tablescreationcursor

DEALLOCATE tablescreationcursor

/* Create the tables */

DECLARE @RowCount int SET @RowCount =

(

SELECT count (*)

FROM ##createtable_final)

DECLARE @Iterator int

SET @Iterator = 1

WHILE @Iterator<= @RowCount

BEGIN SET @Command =

(

SELECT TOP 1

columncreate_final

FROM ##createtable_final)

EXEC (@command)

DELETE ##createtable_final

WHERE columncreate_final = @command

SET @Iterator = @Iterator + 1

END

/* Create primary keys */

DECLARE @TableName varchar(100)

DECLARE @ConstraintName varchar (100)

DECLARE @ColumnName varchar (100)

USE adventureworks2012;

select i.NAME AS constraintname,

object_name(ic.object_id) AS tablename,

col_name(ic.object_id,ic.column_id) AS columnname

INTO ##table_primarykeys

FROM sys.indexes AS i

INNER JOIN sys.index_columns AS ic

ON i.object_id = ic.object_id

AND i.index_id = ic.index_id

WHERE i.is_primary_key = 1

DECLARE tablespkcursor CURSOR FOR

SELECT constraintname,tablename ,columnname

FROM ##table_primarykeys

OPEN tablespkcursor

FETCH next

FROM tablespkcursor

INTO @ConstraintName,@TableName,@ColumnName

WHILE @@FETCH_STATUS = 0

BEGIN

SET @command = ‘USE AdventureWorks2012_temporary; ALTER TABLE [‘ +

@TableName + ‘] ADD CONSTRAINT [‘ + @ConstraintName

+ ‘] PRIMARY KEY CLUSTERED (‘ + @ColumnName + ‘)’

EXEC (@command)

FETCH next

FROM tablespkcursor

INTO @ConstraintName,@TableName ,@ColumnName

END

CLOSE tablespkcursor

DEALLOCATE tablespkcursor

/* Create indexes */

USE adventureworks2012;

select replicate(‘ ‘,4000) AS colnames ,

object_name(i.id) AS tablename,

i.id AS tableid,

i.indid AS indexid,

i.NAME AS indexname,

i.status,

indexproperty (i.id,i.NAME,‘ISUNIQUE’) AS isunique,

indexproperty (i.id,i.NAME,‘ISCLUSTERED’) AS isclustered,

indexproperty (i.id,i.NAME,‘INDEXFILLFACTOR’) AS indexfillfactor

INTO ##indextablelist

FROM sysindexes i

WHERE i.indid > 0

AND i.indid < 255

AND (

i.status & 64)=0

AND i.id>100

AND object_name(i.id) IN

(

SELECT NAME

FROM ##tables_list)

DECLARE @ISQL varchar(4000),

@TABLEID int,

@INDEXID int,

@MAXTABLELENGTH int,

@MAXINDEXLENGTH int

–-USED FOR FORMATTING ONLY

SELECT @MAXTABLELENGTH=max(len(tablename))

FROM ##indextablelist

SELECT @MAXINDEXLENGTH=max(len(indexname))

FROM ##indextablelist

DECLARE c1 CURSOR FOR

SELECT tableid,

indexid

FROM ##indextablelist

OPEN c1

FETCH next

FROM c1

INTO @TABLEID,

@INDEXID

WHILE @@FETCH_STATUS <> -1

BEGIN

SET @ISQL = ''

SELECT @ISQL=@ISQL + isnull(syscolumns.NAME,”) + ‘,’

FROM sysindexes i

INNER JOIN sysindexkeys

ON i.id=sysindexkeys.id

AND i.indid=sysindexkeys.indid

INNER JOIN syscolumns

ON sysindexkeys.id=syscolumns.id

AND sysindexkeys.colid=syscolumns.colid

WHERE i.indid > 0

AND i.indid < 255

AND (

i.status & 64)=0

AND i.id=@TABLEID

AND i.indid=@INDEXID

ORDER BY syscolumns.colid

UPDATE ##indextablelist

SET colnames=@ISQL

WHERE tableid=@TABLEID

AND indexid=@INDEXID

FETCH next

FROM c1

INTO @TABLEID,@INDEXID

END

CLOSE c1

DEALLOCATE c1

-–AT THIS POINT, THE ‘COLNAMES’ COLUMN HAS A TRAILING COMMA

UPDATE ##indextablelist

SET colnames=LEFT(colnames,len(colnames) -1)

SELECT ‘USE AdventureWorks2012_Temporary; CREATE ‘ +

CASE

WHEN isunique = 1 THEN ‘UNIQUE‘

ELSE ‘‘

END +

CASE

WHEN isclustered = 1 THEN ‘ CLUSTERED ‘

ELSE ‘ ‘

END + ‘ INDEX [‘ + upper(indexname) + ‘]’ + space(@MAXINDEXLENGTH -

len(indexname)) +‘ ON [‘ + upper(tablename) + ‘] ‘ + space(@MAXTABLELENGTH -

len(tablename)) + ‘(‘ + upper(colnames) + ‘)’ +

CASE

WHEN indexfillfactor = 0 THEN ‘ ‘

ELSE ‘ WITH FILLFACTOR = ‘ + CONVERT(varchar(10),indexfillfactor)

END AS indexcreate

INTO ##indexcreate

FROM ##indextablelist

/* Create the indexes */

SET @RowCount =(SELECT count (*)FROM ##indexcreate)

SET @Iterator = 1

WHILE @Iterator <= @RowCount

BEGIN

SET @Command =

(

SELECT TOP 1 indexcreate FROM ##indexcreate)

EXEC (@command)

DELETE ##indexcreate

WHERE indexcreate = @command

SET @Iterator = @Iterator + 1

END

Rename LDF and MDF

Posted on 3 februarie 2015 by admin

Răspunde

Here’s a script for renaming your database and the LDF and MDF of it.
– Change path and physical name
ALTER DATABASE AdventureWorks2012 modify FILE (NAME = ‘AdventureWorks2012 ‘, filename =
‘D:\AdventureWorks2012_NEW.mdf’);

ALTER DATABASE AdventureWorks2012 modify FILE (NAME = ‘AdventureWorks2012_log’, filename =
‘D:\AdventureWorks2012_old.ldf’);

– Change logical names
ALTER DATABASE AdventureWorks2012 modify FILE (NAME = AdventureWorks2012 , newname =
AdventureWorks2012 _NEW);

ALTER DATABASE AdventureWorks2012 modify FILE (NAME = AdventureWorks2012 , newname =
AdventureWorks2012 _NEW_log);

– take database offline
ALTER DATABASE AdventureWorks2012 SET offline WITH ROLLBACK immediate

– Rename physical files
DECLARE @command1 VARCHAR (250)

SET @command1 = ‘RENAME D:\AdventureWorks2012 .mdf AdventureWorks2012_NEW.mdf’

EXEC master..Xp_cmdshell @command1

SET @command1 = ‘RENAME D:\AdventureWorks2012 .ldf AdventureWorks2012_NEW.ldf’

EXEC master..Xp_cmdshell @command1

– bring database online
ALTER DATABASE AdventureWorks2012 SET online

– rename database
ALTER DATABASE AdventureWorks2012 modify NAME = AdventureWorks2012_NEW

Configure SQL Server Database Mail – Gmail / Yahoo/ Hotmail

Posted on 18 ianuarie 2015 by admin

Răspunde

What the SQL server database Mail does is to basically send out emails from the SQL Server engine to SMTP servers.

Why would you need that ? Well, let’s see: you might have long running jobs and rather than keeping on checking on them you can set an alert to send you an email when the job is complete. You might need to send out data reports from your database to certain people on a time based period, nothing simpler than that: set a job with a query that will be executed and it’s results will be sent via email to the specified recipients (the results can be displayed in a csv, xls or html file) .

First thing’s first: you need to configure the Database Mail utility. The steps below will show you just how to do that:

1. Go under Management -> Right click Database Mail and select the first option:

2. Then you’ll need to add an SMTP account, you can see below how do add an Gmail , Hotmail and Yahoo SMTP account:

3. Gmail SMTP account:

Email address: your Gmail address
Display Name: the name of the address from which you want your emails to appear from
Gmail SMTP server name: smtp.gmail.com
Gmail SMTP port: 465

4.Yahoo SMTP account:

Yahoo SMTP server name: smtp.mail.yahoo.com
Yahoo SMTP port: 465

5. Hotmail SMTP account:

Hotmail SMTP server name: smtp.live.com
Hotmail SMTP port: 25

6. You just click OK after selecting your SMTP account and completing the rest of the details on the dialog window, as the authentication, in my case i went for the basic authentication

7.You then get to set the your profile as public or private, we are gonna go with public so that every user can send email from this profile without explicitly specifying the profile.

8. Then you’re prompted with the window where you need to configure the system parameters, we’re going to leave everything as it is, apart from the Maximum File Size, the default value is 1.000.000 bytes, which is a little under 1MB. Now if you’re planning to also send attachments via email you’ll see that the default value won’t be enough in most cases, so we’ve set our maximum size to approximately 5 MB, which should suffice in most cases. But if you’re planning to send larger amounts of data out of your server then the Database Mail utility isn’t the way to go.

9. After finishing our wizard you can test you setup by simply right-clicking on the Database Mail utility and selecting Send test email:

If you want other informations about the emails going out of your server you can always use the following scripts: http://www.sqlpentruincepatori.ro/sql-server-database-mail-querys/

Import data from Excel into SQL SERVER – simple steps

Posted on 5 ianuarie 2015 by admin

Răspunde

When trying to import some data from Excel into my test database I over complicated the whole affair, when I could have taken advantage of the Import wizard functionality SSMS offers.

So here are the steps to easily import data from an Excel spreadsheet to your database:

Select your database, then right click and select Tasks -> Import Data…
A window will pop out prompting you to select the Data Source:

Be careful with the ‘First row has column name’ tick box, make sure you select the appropriate choice for you.

Specify where to copy the data to:

4. Click Next to proceed to the Specify Table Copy or Query dialog.

I have selected the first option from above, so now the Select Source Tables and Views dialog window is prompted. In here we can select the specific sheet from where we wish to copy our data and the destination table (you can either create a new table or select an existing one)

You can click the Edit mappings button to review the column mappings if we’re copying the data to an existing table, we can also use the Preview button to view the first 100 rows of the data in the data source.

After we’re ok with the settings we have chosen we press the Next button, another dialog window pops out and we can either execute the package we created or we can also save it for future runs.

Click Next to complete the Import Wizard, and then finish to execute the SSIS package. And then you should see all your data in the tables you’ve selected.

LARGEST tables from the DB

Posted on 1 iulie 2014 by admin

Răspunde

Here’s a quick way to find out the top 20% largest table from your database.

<span class="SQLComment" style="color: #00aa00;">---------------TOP 20% LARGEST tables from the DB----------------</span>
<span class="SQLKeyword" style="color: #0000aa;">CREATE</span> <span class="SQLKeyword" style="color: #0000aa;">TABLE</span> #LargestTable <span class="SQLOperator" style="color: #777777;">(</span>
	TableName <span class="SQLKeyword" style="color: #0000aa;">VARCHAR</span><span class="SQLOperator" style="color: #777777;">(</span>150<span class="SQLOperator" style="color: #777777;">)</span>
	<span class="SQLOperator" style="color: #777777;">,</span>SchemaName <span class="SQLKeyword" style="color: #0000aa;">VARCHAR</span><span class="SQLOperator" style="color: #777777;">(</span>150<span class="SQLOperator" style="color: #777777;">)</span>
	<span class="SQLOperator" style="color: #777777;">,</span>RowCountTotal <span class="SQLKeyword" style="color: #0000aa;">INT</span>
	<span class="SQLOperator" style="color: #777777;">,</span>TotalSpaceKB <span class="SQLKeyword" style="color: #0000aa;">INT</span>
	<span class="SQLOperator" style="color: #777777;">,</span>UsedSpaceKB <span class="SQLKeyword" style="color: #0000aa;">INT</span>
	<span class="SQLOperator" style="color: #777777;">,</span>UnusedSpaceKB <span class="SQLKeyword" style="color: #0000aa;">INT</span>
	<span class="SQLOperator" style="color: #777777;">)</span>

<span class="SQLKeyword" style="color: #0000aa;">INSERT INTO</span> #LargestTable <span class="SQLOperator" style="color: #777777;">(</span>
	TableName
	<span class="SQLOperator" style="color: #777777;">,</span>SchemaName
	<span class="SQLOperator" style="color: #777777;">,</span>RowCountTotal
	<span class="SQLOperator" style="color: #777777;">,</span>TotalSpaceKB
	<span class="SQLOperator" style="color: #777777;">,</span>UsedSpaceKB
	<span class="SQLOperator" style="color: #777777;">,</span>UnusedSpaceKB
	<span class="SQLOperator" style="color: #777777;">)</span>
<span class="SQLKeyword" style="color: #0000aa;">EXECUTE</span> master<span class="SQLOperator" style="color: #777777;">.</span>sys<span class="SQLOperator" style="color: #777777;">.</span>sp_MSforeachdb <span class="SQLString" style="color: #aa0000;">'USE [?]; SELECT
t.NAME AS TableName,
s.Name AS SchemaName,
p.rows AS RowCounts,
SUM(a.total_pages) * 8 AS TotalSpaceKB,
SUM(a.used_pages) * 8 AS UsedSpaceKB,
(SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB
FROM
sys.tables t
INNER JOIN
sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN
sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN
sys.allocation_units a ON p.partition_id = a.container_id
LEFT OUTER JOIN
sys.schemas s ON t.schema_id = s.schema_id
WHERE
t.NAME NOT LIKE ''dt%''
AND t.is_ms_shipped = 0
AND i.OBJECT_ID &gt; 255
GROUP BY
t.Name, s.Name, p.Rows
ORDER BY
t.Name
'</span>

<span class="SQLKeyword" style="color: #0000aa;">SELECT</span> <span class="SQLKeyword" style="color: #0000aa;">TOP</span> <span class="SQLOperator" style="color: #777777;">(</span>20<span class="SQLOperator" style="color: #777777;">)</span> <span class="SQLKeyword" style="color: #0000aa;">PERCENT</span> <span class="SQLOperator" style="color: #777777;">*</span>
<span class="SQLKeyword" style="color: #0000aa;">FROM</span> #LargestTable
<span class="SQLKeyword" style="color: #0000aa;">ORDER</span> <span class="SQLKeyword" style="color: #0000aa;">BY</span> TotalSpaceKB <span class="SQLKeyword" style="color: #0000aa;">DESC</span>

<span class="SQLKeyword" style="color: #0000aa;">DROP</span> <span class="SQLKeyword" style="color: #0000aa;">TABLE</span> #LargestTable

---------------TOP 20% LARGEST tables from the DB----------------

CREATE TABLE #LargestTable (

TableName VARCHAR(150)

,SchemaName VARCHAR(150)

,RowCountTotal INT

,TotalSpaceKB INT

,UsedSpaceKB INT

,UnusedSpaceKB INT

)

INSERT INTO #LargestTable (

TableName

,SchemaName

,RowCountTotal

,TotalSpaceKB

,UsedSpaceKB

,UnusedSpaceKB

)

EXECUTE master.sys.sp_MSforeachdb 'USE [?]; SELECT

t.NAME AS TableName,

s.Name AS SchemaName,

p.rows AS RowCounts,

SUM(a.total_pages) * 8 AS TotalSpaceKB,

SUM(a.used_pages) * 8 AS UsedSpaceKB,

(SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB

FROM

sys.tables t

INNER JOIN

sys.indexes i ON t.OBJECT_ID = i.object_id

INNER JOIN

sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id

INNER JOIN

sys.allocation_units a ON p.partition_id = a.container_id

LEFT OUTER JOIN

sys.schemas s ON t.schema_id = s.schema_id

WHERE

t.NAME NOT LIKE ''dt%''

AND t.is_ms_shipped = 0

AND i.OBJECT_ID > 255

GROUP BY

t.Name, s.Name, p.Rows

ORDER BY

t.Name

'

SELECT TOP (20) PERCENT *

FROM #LargestTable

ORDER BY TotalSpaceKB DESC

DROP TABLE #LargestTable

SELECT TOP (20) PERCENT * FROM #LargestTable ORDER BY TotalSpaceKB DESC
DROP TABLE #LargestTable

Foreign Key and Primary Key dependencies

Posted on 28 iunie 2014 by admin

Răspunde

A way to establish which columns in a given database are joined via the primary and foreign key relationships.

<span class="SQLKeyword" style="color: #0000aa;">
SELECT</span> sys<span class="SQLOperator" style="color: #777777;">.</span>foreign_keys<span class="SQLOperator" style="color: #777777;">.</span><span class="SQLKeyword" style="color: #0000aa;">NAME</span> <span class="SQLString" style="color: #aa0000;">'ForeignKey_Name'</span>
	<span class="SQLOperator" style="color: #777777;">,</span>sys<span class="SQLOperator" style="color: #777777;">.</span>tables<span class="SQLOperator" style="color: #777777;">.</span><span class="SQLKeyword" style="color: #0000aa;">NAME</span> <span class="SQLString" style="color: #aa0000;">'Parent Table'</span>
	<span class="SQLOperator" style="color: #777777;">,</span>sys<span class="SQLOperator" style="color: #777777;">.</span>columns<span class="SQLOperator" style="color: #777777;">.</span><span class="SQLKeyword" style="color: #0000aa;">NAME</span> <span class="SQLString" style="color: #aa0000;">'Column Name'</span>
	<span class="SQLOperator" style="color: #777777;">,</span>sys<span class="SQLOperator" style="color: #777777;">.</span>columns<span class="SQLOperator" style="color: #777777;">.</span>column_id
	<span class="SQLOperator" style="color: #777777;">,</span>st<span class="SQLOperator" style="color: #777777;">.</span><span class="SQLKeyword" style="color: #0000aa;">NAME</span> <span class="SQLString" style="color: #aa0000;">'Refrenced Table'</span>
	<span class="SQLOperator" style="color: #777777;">,</span>sys<span class="SQLOperator" style="color: #777777;">.</span>columns<span class="SQLOperator" style="color: #777777;">.</span><span class="SQLKeyword" style="color: #0000aa;">NAME</span> <span class="SQLString" style="color: #aa0000;">'Refernced Column'</span>
	<span class="SQLOperator" style="color: #777777;">,</span>sys<span class="SQLOperator" style="color: #777777;">.</span>columns<span class="SQLOperator" style="color: #777777;">.</span>column_id
<span class="SQLKeyword" style="color: #0000aa;">FROM</span> sys<span class="SQLOperator" style="color: #777777;">.</span>foreign_keys
<span class="SQLKeyword" style="color: #0000aa;">INNER JOIN</span> sys<span class="SQLOperator" style="color: #777777;">.</span>tables
	<span class="SQLKeyword" style="color: #0000aa;">ON</span> sys<span class="SQLOperator" style="color: #777777;">.</span>foreign_keys<span class="SQLOperator" style="color: #777777;">.</span>parent_object_id <span class="SQLOperator" style="color: #777777;">=</span> sys<span class="SQLOperator" style="color: #777777;">.</span>tables<span class="SQLOperator" style="color: #777777;">.</span><span class="SQLFunction" style="color: #aa00aa;">object_id</span>
<span class="SQLKeyword" style="color: #0000aa;">INNER JOIN</span> sys<span class="SQLOperator" style="color: #777777;">.</span>tables st
	<span class="SQLKeyword" style="color: #0000aa;">ON</span> sys<span class="SQLOperator" style="color: #777777;">.</span>foreign_keys<span class="SQLOperator" style="color: #777777;">.</span>referenced_object_id <span class="SQLOperator" style="color: #777777;">=</span> st<span class="SQLOperator" style="color: #777777;">.</span><span class="SQLFunction" style="color: #aa00aa;">object_id</span>
<span class="SQLKeyword" style="color: #0000aa;">INNER JOIN</span> sys<span class="SQLOperator" style="color: #777777;">.</span>foreign_key_columns
	<span class="SQLKeyword" style="color: #0000aa;">ON</span> sys<span class="SQLOperator" style="color: #777777;">.</span>foreign_key_columns<span class="SQLOperator" style="color: #777777;">.</span>constraint_object_id <span class="SQLOperator" style="color: #777777;">=</span> sys<span class="SQLOperator" style="color: #777777;">.</span>foreign_keys<span class="SQLOperator" style="color: #777777;">.</span><span class="SQLFunction" style="color: #aa00aa;">object_id</span>
<span class="SQLKeyword" style="color: #0000aa;">INNER JOIN</span> sys<span class="SQLOperator" style="color: #777777;">.</span>columns
	<span class="SQLKeyword" style="color: #0000aa;">ON</span> sys<span class="SQLOperator" style="color: #777777;">.</span>foreign_key_columns<span class="SQLOperator" style="color: #777777;">.</span>parent_column_id <span class="SQLOperator" style="color: #777777;">=</span> sys<span class="SQLOperator" style="color: #777777;">.</span>columns<span class="SQLOperator" style="color: #777777;">.</span>column_id
		<span class="SQLKeyword" style="color: #0000aa;">AND</span> sys<span class="SQLOperator" style="color: #777777;">.</span>foreign_keys<span class="SQLOperator" style="color: #777777;">.</span>parent_object_id <span class="SQLOperator" style="color: #777777;">=</span> sys<span class="SQLOperator" style="color: #777777;">.</span>columns<span class="SQLOperator" style="color: #777777;">.</span><span class="SQLFunction" style="color: #aa00aa;">object_id</span>
<span class="SQLKeyword" style="color: #0000aa;">INNER JOIN</span> sys<span class="SQLOperator" style="color: #777777;">.</span>columns sc
	<span class="SQLKeyword" style="color: #0000aa;">ON</span> sys<span class="SQLOperator" style="color: #777777;">.</span>foreign_key_columns<span class="SQLOperator" style="color: #777777;">.</span>referenced_column_id <span class="SQLOperator" style="color: #777777;">=</span> sc<span class="SQLOperator" style="color: #777777;">.</span>column_id
		<span class="SQLKeyword" style="color: #0000aa;">AND</span> sys<span class="SQLOperator" style="color: #777777;">.</span>foreign_keys<span class="SQLOperator" style="color: #777777;">.</span>referenced_object_id <span class="SQLOperator" style="color: #777777;">=</span> sc<span class="SQLOperator" style="color: #777777;">.</span><span class="SQLFunction" style="color: #aa00aa;">object_id</span>
<span class="SQLKeyword" style="color: #0000aa;">ORDER</span> <span class="SQLKeyword" style="color: #0000aa;">BY</span> sys<span class="SQLOperator" style="color: #777777;">.</span>tables<span class="SQLOperator" style="color: #777777;">.</span><span class="SQLKeyword" style="color: #0000aa;">NAME</span>

SELECT sys.foreign_keys.NAME 'ForeignKey_Name'

,sys.tables.NAME 'Parent Table'

,sys.columns.NAME 'Column Name'

,sys.columns.column_id

,st.NAME 'Refrenced Table'

,sys.columns.column_id

FROM sys.foreign_keys

INNER JOIN sys.tables

ON sys.foreign_keys.parent_object_id = sys.tables.object_id

INNER JOIN sys.tables st

ON sys.foreign_keys.referenced_object_id = st.object_id

INNER JOIN sys.foreign_key_columns

ON sys.foreign_key_columns.constraint_object_id = sys.foreign_keys.object_id

INNER JOIN sys.columns

ON sys.foreign_key_columns.parent_column_id = sys.columns.column_id

AND sys.foreign_keys.parent_object_id = sys.columns.object_id

INNER JOIN sys.columns sc

ON sys.foreign_key_columns.referenced_column_id = sc.column_id

AND sys.foreign_keys.referenced_object_id = sc.object_id

ORDER BY sys.tables.NAME

Capacity Planning

Posted on 20 iunie 2014 by admin

Răspunde

Here is a nice little script that will tell you everything you need to know in terms of the sizes of your tables, will perform an analysis on each table for each database. Hope it helps

<span class="SQLKeyword" style="color: #0000aa;">CREATE</span> <span class="SQLKeyword" style="color: #0000aa;">TABLE</span> #CapacityPlanningInfo <span class="SQLOperator" style="color: #777777;">(</span>
	[DatabaseName] <span class="SQLKeyword" style="color: #0000aa;">VARCHAR</span><span class="SQLOperator" style="color: #777777;">(</span>150<span class="SQLOperator" style="color: #777777;">)</span>
	<span class="SQLOperator" style="color: #777777;">,</span>[Size in MB] <span class="SQLKeyword" style="color: #0000aa;">DECIMAL</span><span class="SQLOperator" style="color: #777777;">(</span>10<span class="SQLOperator" style="color: #777777;">,</span> 2<span class="SQLOperator" style="color: #777777;">)</span>
	<span class="SQLOperator" style="color: #777777;">,</span>[Space Used] <span class="SQLKeyword" style="color: #0000aa;">DECIMAL</span><span class="SQLOperator" style="color: #777777;">(</span>10<span class="SQLOperator" style="color: #777777;">,</span> 2<span class="SQLOperator" style="color: #777777;">)</span>
	<span class="SQLOperator" style="color: #777777;">,</span>[Available Space] <span class="SQLKeyword" style="color: #0000aa;">DECIMAL</span><span class="SQLOperator" style="color: #777777;">(</span>10<span class="SQLOperator" style="color: #777777;">,</span> 2<span class="SQLOperator" style="color: #777777;">)</span>
	<span class="SQLOperator" style="color: #777777;">,</span>[Percent Used] <span class="SQLKeyword" style="color: #0000aa;">DECIMAL</span><span class="SQLOperator" style="color: #777777;">(</span>10<span class="SQLOperator" style="color: #777777;">,</span> 2<span class="SQLOperator" style="color: #777777;">)</span>
	<span class="SQLOperator" style="color: #777777;">,</span>TableName <span class="SQLKeyword" style="color: #0000aa;">VARCHAR</span><span class="SQLOperator" style="color: #777777;">(</span>150<span class="SQLOperator" style="color: #777777;">)</span>
	<span class="SQLOperator" style="color: #777777;">,</span>SchemaName <span class="SQLKeyword" style="color: #0000aa;">VARCHAR</span><span class="SQLOperator" style="color: #777777;">(</span>150<span class="SQLOperator" style="color: #777777;">)</span>
	<span class="SQLOperator" style="color: #777777;">,</span>RowCountTotal <span class="SQLKeyword" style="color: #0000aa;">INT</span>
	<span class="SQLOperator" style="color: #777777;">,</span>TotalSpaceKB <span class="SQLKeyword" style="color: #0000aa;">INT</span>
	<span class="SQLOperator" style="color: #777777;">,</span>UsedSpaceKB <span class="SQLKeyword" style="color: #0000aa;">INT</span>
	<span class="SQLOperator" style="color: #777777;">,</span>UnusedSpaceKB <span class="SQLKeyword" style="color: #0000aa;">INT</span>
	<span class="SQLOperator" style="color: #777777;">,</span>LoadTime <span class="SQLKeyword" style="color: #0000aa;">DATETIME</span>
	<span class="SQLOperator" style="color: #777777;">)</span>

<span class="SQLKeyword" style="color: #0000aa;">INSERT INTO</span> #CapacityPlanningInfo <span class="SQLOperator" style="color: #777777;">(</span>
	[DatabaseName]
	<span class="SQLOperator" style="color: #777777;">,</span>[Size in MB]
	<span class="SQLOperator" style="color: #777777;">,</span>[Space Used]
	<span class="SQLOperator" style="color: #777777;">,</span>[Available Space]
	<span class="SQLOperator" style="color: #777777;">,</span>[Percent Used]
	<span class="SQLOperator" style="color: #777777;">,</span>TableName
	<span class="SQLOperator" style="color: #777777;">,</span>SchemaName
	<span class="SQLOperator" style="color: #777777;">,</span>RowCountTotal
	<span class="SQLOperator" style="color: #777777;">,</span>TotalSpaceKB
	<span class="SQLOperator" style="color: #777777;">,</span>UsedSpaceKB
	<span class="SQLOperator" style="color: #777777;">,</span>UnusedSpaceKB
	<span class="SQLOperator" style="color: #777777;">,</span>LoadTime
	<span class="SQLOperator" style="color: #777777;">)</span>
<span class="SQLKeyword" style="color: #0000aa;">EXECUTE</span> master<span class="SQLOperator" style="color: #777777;">.</span>sys<span class="SQLOperator" style="color: #777777;">.</span>sp_MSforeachdb 
	<span class="SQLString" style="color: #aa0000;">'USE [?]; SELECT
DB_Name () as DatabaseName,
CAST(size/128.0 AS DECIMAL(10,2)) AS [Size in MB],
CAST(FILEPROPERTY(sf.name, ''SpaceUsed'')/128.0 AS DECIMAL(10,2)) AS [Space Used],
CAST(size/128.0-(FILEPROPERTY(sf.name, ''SpaceUsed'')/128.0) AS DECIMAL(10,2)) AS [Available Space],
CAST((CAST(FILEPROPERTY(sf.name, ''SpaceUsed'')/128.0 AS DECIMAL(10,2))/CAST(size/128.0 AS DECIMAL(10,2)))*100 AS DECIMAL(10,2)) AS [Percent Used],
t.NAME AS TableName,
s.Name AS SchemaName,
p.rows AS RowCounts,
SUM(a.total_pages) * 8 AS TotalSpaceKB,
SUM(a.used_pages) * 8 AS UsedSpaceKB,
(SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB,
GetDate ()
FROM
sys.tables t
INNER JOIN
sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN
sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN
sys.allocation_units a ON p.partition_id = a.container_id
INNER JOIN sysfiles sf on DB_Name() = sf.name
LEFT OUTER JOIN
sys.schemas s ON t.schema_id = s.schema_id
WHERE
t.NAME NOT LIKE ''dt%''
AND t.is_ms_shipped = 0
AND i.OBJECT_ID &gt; 255
GROUP BY
sf.name,t.Name, s.Name, p.Rows, sf.size
ORDER BY
t.Name'

</span>

CREATE TABLE #CapacityPlanningInfo (

[DatabaseName] VARCHAR(150)

,[Size in MB] DECIMAL(10, 2)

,[Space Used] DECIMAL(10, 2)

,[Available Space] DECIMAL(10, 2)

,[Percent Used] DECIMAL(10, 2)

,TableName VARCHAR(150)

,RowCountTotal INT

,TotalSpaceKB INT

,UsedSpaceKB INT

,UnusedSpaceKB INT

,LoadTime DATETIME

)

INSERT INTO #CapacityPlanningInfo (

[DatabaseName]

,[Size in MB]

,[Space Used]

,[Available Space]

,[Percent Used]

,TableName

,SchemaName

,RowCountTotal

,TotalSpaceKB

,UsedSpaceKB

,UnusedSpaceKB

,LoadTime

)

'USE [?]; SELECT

DB_Name () as DatabaseName,

CAST(size/128.0 AS DECIMAL(10,2)) AS [Size in MB],

CAST(FILEPROPERTY(sf.name, ''SpaceUsed'')/128.0 AS DECIMAL(10,2)) AS [Space Used],

CAST(size/128.0-(FILEPROPERTY(sf.name, ''SpaceUsed'')/128.0) AS DECIMAL(10,2)) AS [Available Space],

CAST((CAST(FILEPROPERTY(sf.name, ''SpaceUsed'')/128.0 AS DECIMAL(10,2))/CAST(size/128.0 AS DECIMAL(10,2)))*100 AS DECIMAL(10,2)) AS [Percent Used],

t.NAME AS TableName,

s.Name AS SchemaName,

p.rows AS RowCounts,

SUM(a.total_pages) * 8 AS TotalSpaceKB,

SUM(a.used_pages) * 8 AS UsedSpaceKB,

(SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB,

GetDate ()

FROM

sys.tables t

INNER JOIN

sys.indexes i ON t.OBJECT_ID = i.object_id

INNER JOIN

sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id

INNER JOIN

sys.allocation_units a ON p.partition_id = a.container_id

INNER JOIN sysfiles sf on DB_Name() = sf.name

LEFT OUTER JOIN

sys.schemas s ON t.schema_id = s.schema_id

WHERE

t.NAME NOT LIKE ''dt%''

AND t.is_ms_shipped = 0

AND i.OBJECT_ID > 255

GROUP BY

sf.name,t.Name, s.Name, p.Rows, sf.size

ORDER BY

t.Name'

Most populated tables from the database SQL SERVER

Posted on 5 iunie 2014 by admin

Răspunde

In case you need to find a list of the most populated tables within your database here you have a very useful script:

<span class="SQLKeyword" style="color: #0000aa;">CREATE</span> <span class="SQLKeyword" style="color: #0000aa;">TABLE</span> #CapacityTable <span class="SQLOperator" style="color: #777777;">(</span>
	TableName <span class="SQLKeyword" style="color: #0000aa;">VARCHAR</span><span class="SQLOperator" style="color: #777777;">(</span>150<span class="SQLOperator" style="color: #777777;">)</span>
	<span class="SQLOperator" style="color: #777777;">,</span>RowCountTotal <span class="SQLKeyword" style="color: #0000aa;">INT</span>
	<span class="SQLOperator" style="color: #777777;">)</span>

<span class="SQLKeyword" style="color: #0000aa;">INSERT INTO</span> #CapacityTable <span class="SQLOperator" style="color: #777777;">(</span>
	TableName
	<span class="SQLOperator" style="color: #777777;">,</span>RowCountTotal
	<span class="SQLOperator" style="color: #777777;">)</span>
<span class="SQLKeyword" style="color: #0000aa;">EXECUTE</span> master<span class="SQLOperator" style="color: #777777;">.</span>sys<span class="SQLOperator" style="color: #777777;">.</span>sp_MSforeachdb ‘USE [?]<span class="SQLOperator" style="color: #777777;">;</span>

<span class="SQLKeyword" style="color: #0000aa;">SELECT</span> sys<span class="SQLOperator" style="color: #777777;">.</span>schemas<span class="SQLOperator" style="color: #777777;">.</span><span class="SQLKeyword" style="color: #0000aa;">NAME</span> <span class="SQLOperator" style="color: #777777;">+</span> ”<span class="SQLOperator" style="color: #777777;">.</span>” <span class="SQLOperator" style="color: #777777;">+</span> sys<span class="SQLOperator" style="color: #777777;">.</span>tables<span class="SQLOperator" style="color: #777777;">.</span><span class="SQLKeyword" style="color: #0000aa;">NAME</span> TableName
	<span class="SQLOperator" style="color: #777777;">,</span><span class="SQLFunction" style="color: #aa00aa;">SUM</span><span class="SQLOperator" style="color: #777777;">(</span>sys<span class="SQLOperator" style="color: #777777;">.</span>partitions<span class="SQLOperator" style="color: #777777;">.</span>rows<span class="SQLOperator" style="color: #777777;">)</span> RowCountTotal
<span class="SQLKeyword" style="color: #0000aa;">FROM</span> sys<span class="SQLOperator" style="color: #777777;">.</span>tables
<span class="SQLKeyword" style="color: #0000aa;">INNER JOIN</span> sys<span class="SQLOperator" style="color: #777777;">.</span>partitions <span class="SQLKeyword" style="color: #0000aa;">ON</span> sys<span class="SQLOperator" style="color: #777777;">.</span>partitions<span class="SQLOperator" style="color: #777777;">.</span><span class="SQLFunction" style="color: #aa00aa;">OBJECT_ID</span> <span class="SQLOperator" style="color: #777777;">=</span> sys<span class="SQLOperator" style="color: #777777;">.</span>tables<span class="SQLOperator" style="color: #777777;">.</span><span class="SQLFunction" style="color: #aa00aa;">OBJECT_ID</span>
<span class="SQLKeyword" style="color: #0000aa;">INNER JOIN</span> sys<span class="SQLOperator" style="color: #777777;">.</span>schemas <span class="SQLKeyword" style="color: #0000aa;">ON</span> sys<span class="SQLOperator" style="color: #777777;">.</span>tables<span class="SQLOperator" style="color: #777777;">.</span><span class="SQLFunction" style="color: #aa00aa;">schema_id</span> <span class="SQLOperator" style="color: #777777;">=</span> sys<span class="SQLOperator" style="color: #777777;">.</span>schemas<span class="SQLOperator" style="color: #777777;">.</span><span class="SQLFunction" style="color: #aa00aa;">schema_id</span> –WHERE sys<span class="SQLOperator" style="color: #777777;">.</span>tables<span class="SQLOperator" style="color: #777777;">.</span>is_ms_shipped <span class="SQLOperator" style="color: #777777;">=</span> 0
	<span class="SQLKeyword" style="color: #0000aa;">AND</span> sys<span class="SQLOperator" style="color: #777777;">.</span>partitions<span class="SQLOperator" style="color: #777777;">.</span>index_id <span class="SQLOperator" style="color: #777777;">IN</span> <span class="SQLOperator" style="color: #777777;">(</span>
		1
		<span class="SQLOperator" style="color: #777777;">,</span>0
		<span class="SQLOperator" style="color: #777777;">)</span> – 0 <span class="SQLOperator" style="color: #777777;">=</span> heap
	<span class="SQLOperator" style="color: #777777;">,</span>1 <span class="SQLOperator" style="color: #777777;">=</span> <span class="SQLKeyword" style="color: #0000aa;">CLUSTERED</span> <span class="SQLKeyword" style="color: #0000aa;">INDEX</span>
	<span class="SQLOperator" style="color: #777777;">,</span><span class="SQLOperator" style="color: #777777;">&gt;</span> 1 <span class="SQLKeyword" style="color: #0000aa;">NONCLUSTERED</span> <span class="SQLKeyword" style="color: #0000aa;">INDEX</span> – is_ms_shipped<span class="SQLOperator" style="color: #777777;">(</span><span class="SQLKeyword" style="color: #0000aa;">BIT</span><span class="SQLOperator" style="color: #777777;">)</span> <span class="SQLOperator" style="color: #777777;">=</span> <span class="SQLKeyword" style="color: #0000aa;">OBJECT</span> <span class="SQLKeyword" style="color: #0000aa;">IS</span> created <span class="SQLKeyword" style="color: #0000aa;">BY</span> an internal SQL <span class="SQLKeyword" style="color: #0000aa;">SERVER</span> component
<span class="SQLKeyword" style="color: #0000aa;">GROUP</span> <span class="SQLKeyword" style="color: #0000aa;">BY</span> sys<span class="SQLOperator" style="color: #777777;">.</span>schemas<span class="SQLOperator" style="color: #777777;">.</span><span class="SQLKeyword" style="color: #0000aa;">NAME</span>
	<span class="SQLOperator" style="color: #777777;">,</span>sys<span class="SQLOperator" style="color: #777777;">.</span>tables<span class="SQLOperator" style="color: #777777;">.</span><span class="SQLKeyword" style="color: #0000aa;">NAME</span>
<span class="SQLKeyword" style="color: #0000aa;">ORDER</span> <span class="SQLKeyword" style="color: #0000aa;">BY</span> <span class="SQLFunction" style="color: #aa00aa;">SUM</span><span class="SQLOperator" style="color: #777777;">(</span>sys<span class="SQLOperator" style="color: #777777;">.</span>partitions<span class="SQLOperator" style="color: #777777;">.</span>rows<span class="SQLOperator" style="color: #777777;">)</span> <span class="SQLKeyword" style="color: #0000aa;">DESC</span> ‘

<span class="SQLKeyword" style="color: #0000aa;">SELECT</span> <span class="SQLOperator" style="color: #777777;">*</span>
<span class="SQLKeyword" style="color: #0000aa;">FROM</span> #CapacityTable
<span class="SQLKeyword" style="color: #0000aa;">ORDER</span> <span class="SQLKeyword" style="color: #0000aa;">BY</span> RowCountTotal <span class="SQLKeyword" style="color: #0000aa;">DESC</span>

<span class="SQLKeyword" style="color: #0000aa;">DROP</span> <span class="SQLKeyword" style="color: #0000aa;">TABLE</span> #CapacityTable

CREATE TABLE #CapacityTable (

TableName VARCHAR(150)

,RowCountTotal INT

)

INSERT INTO #CapacityTable (

TableName

,RowCountTotal

)

EXECUTE master.sys.sp_MSforeachdb ‘USE [?];

SELECT sys.schemas.NAME + ”.” + sys.tables.NAME TableName

,SUM(sys.partitions.rows) RowCountTotal

FROM sys.tables

INNER JOIN sys.partitions ON sys.partitions.OBJECT_ID = sys.tables.OBJECT_ID

INNER JOIN sys.schemas ON sys.tables.schema_id = sys.schemas.schema_id –WHERE sys.tables.is_ms_shipped = 0

AND sys.partitions.index_id IN (

,0

) – 0 = heap

,1 = CLUSTERED INDEX

,> 1 NONCLUSTERED INDEX – is_ms_shipped(BIT) = OBJECT IS created BY an internal SQL SERVER component

GROUP BY sys.schemas.NAME

ORDER BY SUM(sys.partitions.rows) DESC ‘

SELECT *

FROM #CapacityTable

ORDER BY RowCountTotal DESC

DROP TABLE #CapacityTable

SQL Server database mail queries

Posted on 28 mai 2014 by admin

Useful scripts to query the SQL Server for troubleshooting database mail problems:

<span class="SQLComment" style="color: #00aa00;">--Profiles</span>
<span class="SQLKeyword" style="color: #0000aa;">SELECT</span> <span class="SQLOperator" style="color: #777777;">*</span>
<span class="SQLKeyword" style="color: #0000aa;">FROM</span> msdb<span class="SQLOperator" style="color: #777777;">.</span>dbo<span class="SQLOperator" style="color: #777777;">.</span>sysmail_profile

<span class="SQLComment" style="color: #00aa00;">--Accounts</span>
<span class="SQLKeyword" style="color: #0000aa;">SELECT</span> <span class="SQLOperator" style="color: #777777;">*</span>
<span class="SQLKeyword" style="color: #0000aa;">FROM</span> msdb<span class="SQLOperator" style="color: #777777;">.</span>dbo<span class="SQLOperator" style="color: #777777;">.</span>sysmail_account

<span class="SQLComment" style="color: #00aa00;">--Profile Accounts</span>
<span class="SQLKeyword" style="color: #0000aa;">SELECT</span> <span class="SQLOperator" style="color: #777777;">*</span>
<span class="SQLKeyword" style="color: #0000aa;">FROM</span> msdb<span class="SQLOperator" style="color: #777777;">.</span>dbo<span class="SQLOperator" style="color: #777777;">.</span>sysmail_profileaccount

<span class="SQLComment" style="color: #00aa00;">--Principal Profile</span>
<span class="SQLKeyword" style="color: #0000aa;">SELECT</span> <span class="SQLOperator" style="color: #777777;">*</span>
<span class="SQLKeyword" style="color: #0000aa;">FROM</span> msdb<span class="SQLOperator" style="color: #777777;">.</span>dbo<span class="SQLOperator" style="color: #777777;">.</span>sysmail_principalprofile

<span class="SQLComment" style="color: #00aa00;">--Mail Server</span>
<span class="SQLKeyword" style="color: #0000aa;">SELECT</span> <span class="SQLOperator" style="color: #777777;">*</span>
<span class="SQLKeyword" style="color: #0000aa;">FROM</span> msdb<span class="SQLOperator" style="color: #777777;">.</span>dbo<span class="SQLOperator" style="color: #777777;">.</span>sysmail_server

<span class="SQLKeyword" style="color: #0000aa;">SELECT</span> <span class="SQLOperator" style="color: #777777;">*</span>
<span class="SQLKeyword" style="color: #0000aa;">FROM</span> msdb<span class="SQLOperator" style="color: #777777;">.</span>dbo<span class="SQLOperator" style="color: #777777;">.</span>sysmail_servertype

<span class="SQLKeyword" style="color: #0000aa;">SELECT</span> <span class="SQLOperator" style="color: #777777;">*</span>
<span class="SQLKeyword" style="color: #0000aa;">FROM</span> msdb<span class="SQLOperator" style="color: #777777;">.</span>dbo<span class="SQLOperator" style="color: #777777;">.</span>sysmail_configuration

<span class="SQLComment" style="color: #00aa00;">--Email Sent Status</span>
<span class="SQLKeyword" style="color: #0000aa;">SELECT</span> <span class="SQLOperator" style="color: #777777;">*</span>
<span class="SQLKeyword" style="color: #0000aa;">FROM</span> msdb<span class="SQLOperator" style="color: #777777;">.</span>dbo<span class="SQLOperator" style="color: #777777;">.</span>sysmail_allitems ss
<span class="SQLKeyword" style="color: #0000aa;">ORDER</span> <span class="SQLKeyword" style="color: #0000aa;">BY</span> ss<span class="SQLOperator" style="color: #777777;">.</span>sent_date <span class="SQLKeyword" style="color: #0000aa;">DESC</span>

<span class="SQLKeyword" style="color: #0000aa;">SELECT</span> <span class="SQLOperator" style="color: #777777;">*</span>
<span class="SQLKeyword" style="color: #0000aa;">FROM</span> msdb<span class="SQLOperator" style="color: #777777;">.</span>dbo<span class="SQLOperator" style="color: #777777;">.</span>sysmail_sentitems ss
<span class="SQLKeyword" style="color: #0000aa;">ORDER</span> <span class="SQLKeyword" style="color: #0000aa;">BY</span> ss<span class="SQLOperator" style="color: #777777;">.</span>sent_date <span class="SQLKeyword" style="color: #0000aa;">DESC</span>

<span class="SQLKeyword" style="color: #0000aa;">SELECT</span> <span class="SQLOperator" style="color: #777777;">*</span>
<span class="SQLKeyword" style="color: #0000aa;">FROM</span> msdb<span class="SQLOperator" style="color: #777777;">.</span>dbo<span class="SQLOperator" style="color: #777777;">.</span>sysmail_unsentitems

<span class="SQLKeyword" style="color: #0000aa;">SELECT</span> <span class="SQLOperator" style="color: #777777;">*</span>
<span class="SQLKeyword" style="color: #0000aa;">FROM</span> msdb<span class="SQLOperator" style="color: #777777;">.</span>dbo<span class="SQLOperator" style="color: #777777;">.</span>sysmail_faileditems

<span class="SQLComment" style="color: #00aa00;">--Email Status</span>
<span class="SQLKeyword" style="color: #0000aa;">SELECT</span> <span class="SQLFunction" style="color: #aa00aa;">SUBSTRING</span><span class="SQLOperator" style="color: #777777;">(</span>fail<span class="SQLOperator" style="color: #777777;">.</span>subject<span class="SQLOperator" style="color: #777777;">,</span> 1<span class="SQLOperator" style="color: #777777;">,</span> 25<span class="SQLOperator" style="color: #777777;">)</span> <span class="SQLKeyword" style="color: #0000aa;">AS</span> <span class="SQLString" style="color: #aa0000;">'Subject'</span>
	<span class="SQLOperator" style="color: #777777;">,</span>fail<span class="SQLOperator" style="color: #777777;">.</span>mailitem_id
	<span class="SQLOperator" style="color: #777777;">,</span><span class="SQLFunction" style="color: #aa00aa;">LOG</span><span class="SQLOperator" style="color: #777777;">.</span>description
<span class="SQLKeyword" style="color: #0000aa;">FROM</span> msdb<span class="SQLOperator" style="color: #777777;">.</span>dbo<span class="SQLOperator" style="color: #777777;">.</span>sysmail_event_log <span class="SQLFunction" style="color: #aa00aa;">LOG</span>
<span class="SQLKeyword" style="color: #0000aa;">INNER JOIN</span> msdb<span class="SQLOperator" style="color: #777777;">.</span>dbo<span class="SQLOperator" style="color: #777777;">.</span>sysmail_faileditems fail
	<span class="SQLKeyword" style="color: #0000aa;">ON</span> fail<span class="SQLOperator" style="color: #777777;">.</span>mailitem_id <span class="SQLOperator" style="color: #777777;">=</span> <span class="SQLFunction" style="color: #aa00aa;">LOG</span><span class="SQLOperator" style="color: #777777;">.</span>mailitem_id
<span class="SQLKeyword" style="color: #0000aa;">WHERE</span> event_type <span class="SQLOperator" style="color: #777777;">=</span> <span class="SQLString" style="color: #aa0000;">'error'</span>

<span class="SQLComment" style="color: #00aa00;">--Mail Queues</span>
<span class="SQLKeyword" style="color: #0000aa;">EXEC</span> msdb<span class="SQLOperator" style="color: #777777;">.</span>dbo<span class="SQLOperator" style="color: #777777;">.</span>sysmail_help_queue_sp

--Profiles

SELECT *

FROM msdb.dbo.sysmail_profile

--Accounts

SELECT *

FROM msdb.dbo.sysmail_account

--Profile Accounts

SELECT *

FROM msdb.dbo.sysmail_profileaccount

--Principal Profile

SELECT *

--Mail Server

SELECT *

FROM msdb.dbo.sysmail_server

SELECT *

--Email Sent Status

SELECT *

ORDER BY ss.sent_date DESC

SELECT *

--Email Status

SELECT SUBSTRING(fail.subject, 1, 25) AS 'Subject'

,fail.mailitem_id

,LOG.description

INNER JOIN msdb.dbo.sysmail_faileditems fail

ON fail.mailitem_id = LOG.mailitem_id

WHERE event_type = 'error'

--Mail Queues

EXEC msdb.dbo.sysmail_help_queue_sp

” is not a valid login or you don’t have permission – SQL Server 2008 R2

Posted on 26 mai 2014 by admin

Răspunde

Yesterday I spent 4 hours trying to install SQL Server on my newly installed Windows 8, and I kept receiving the same error: ” is not a valid login or you don’t have permission.

At first when I started the installation of SQL Server, it did complain about compatibility issues, but I chose to ignore it as I thought it might have to do with the fact it might not have properly mounted my ISO image of the SQL Server.

And just when I was about to reinstall Windows after trying on several things I came across the solution to fix this: my computer name and the username are the same. This interferes with the Windows Authentication for SQL Server. My username on Windows 8 was Olia\Olia.

After changing my computer name to Olia-PC I’ve reinstalled the SQL Server and it worked like a charm.

SQL pentru incepatori

Invata SQL de la 0

Script out database – tables with indexes and primary keys

Rename LDF and MDF

Configure SQL Server Database Mail – Gmail / Yahoo/ Hotmail

Import data from Excel into SQL SERVER – simple steps

LARGEST tables from the DB

Foreign Key and Primary Key dependencies

Capacity Planning

Most populated tables from the database SQL SERVER

SQL Server database mail queries

” is not a valid login or you don’t have permission – SQL Server 2008 R2