Category Archives: Sql scripts

data files and filegroups per database…


Hi All,

Use the below code to get the data files and groups information per database

 

SELECT

COUNT (*) AS [DataFiles],

COUNT (DISTINCT data_space_id) AS [Filegroups],4
SUM (size) AS [Size]

FROM sys.master_files

WHERE [type_desc] = N’ROWS’ — filter out log files/data_space_id 0

AND [database_id] > 4 — filter out system databases
AND [FILE_ID] != 65537 — filter out FILESTREAM

GROUP BY [database_id];
GO

 

Please check and let us know if you have any concerns on this.

Thanks for viewing this.

Regards,

Chaitanya

Advertisements

SCRIPT finding the Size of required tables in Sql Server Database


Hi All,

Today we got one requirement to find the size of given tables in sql database

We have written the below script and its working fine

USE AdventureWorks2012

GO

SELECT t.name

AS

TableName,

p.rows

AS RowCounts,

Sum(((( Cast(a.used_pages AS DECIMAL(18, 4)) * 8192 ) / 1024 ) / 1024 )) AS ‘File Size (MB)’,

Sum(((( Cast(a.used_pages AS DECIMAL(18, 4)) * 8192 ) / 1024 ) / 1024 ) / 1024)AS ‘File Size (GB)’,

Sum((((( Cast(a.used_pages AS DECIMAL(18, 4)) * 8192 ) / 1024 ) / 1024 ) / 1024 / 1024 ))AS ‘File Size (TB)’

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

WHERE t.name IN(‘ErrorLog’,’DatabaseLog’)

AND t.is_ms_shipped = 0

AND i.object_id > 255

GROUP BY t.name,

p.rows

ORDER BY t.name

Result:

We can get the size of tables in MB,GB’s and TB’s

Thanks for viewing this.

Regards,

Chaitanya

Visit site: http://www.sqlblogging.com

Send an Email: sqlblogging

@@CONNECTIONS and @@MAX_CONNECTIONS in sql server


How to find number of connection attempts to sql server since last restart

@@CONNECTIONS: Global variable is used for finding this information

Syntax:

Select @@CONNECTIONS

Return Typeàinteger

After Sql server instance is restarted we have these many attempts are made to connect to sql server instance and these attempts are success or failed

And it is non-Deterministic as it may return different results each time they are called with a specific set of input values.

We have another global variable @@MAX_CONNECTIONS and this is used to check how many connections can be done to sql server simultaniously

Syntax:

Select @@MAX_CONNECTIONS

Return Typeàinteger

Simultaniosly users can open 32767 connctions to sql server instance

The actual number of user connections allowed also depends on the version of SQL Server installed and the limitations of your application(s) and hardware

Regards,

Chaitanya,

Webiste : https://mssqlbuzz.wordpress.com/

To Send Mail : mssqlbuzz

Searching the strings with leading spaces and trailing spaces in the sql server


Hi Folks,

We have issues in the production at our end where the master table is update with leading spaces and it is causing the incorrect results as it master table and configuration is present in those tables.

I quickly checked the leading spaces in the table using the below query

We have created sample tables for this demo

use tempdb

go

create table #tempo

(

id int,

name varchar(100)

)

insert #tempo

select 1,’chaitu’

union all

select 2,’pradee’

union all

select ‘3’,’ krishna’

select * from #tempo

Below is the query for getting the leading spaces in the name column

select * from #tempo where LEFT(name,1)=”

For trailing spaces

use tempdb

go

create table #tempo

(

id int,

name varchar(100)

)

insert #tempo

select 1,’chaitu’

union all

select 2,’pradee’

union all

select ‘3’,’krishna ‘ à trailing space is inserted

select * from #tempo

Below is the query for getting the tralining spaces in the name column

select * from #tempo where right(name,1)=”

Please let us know if you have other options

Thanks for viewing.

Regards,

Chaitanya,

https://mssqlbuzz.wordpress.com/

Number of ways to view the tables and views in the sql server


sp_tables is the procedure used for getting the tables and views information in sql server current data base

sp_tables [ [ @table_name = ] ‘name’ ]

[ , [ @table_owner = ] ‘owner’ ]

[ , [ @table_qualifier = ] ‘qualifier’ ]

[ , [ @table_type = ] “type” ]

[ , [@fUsePattern = ] ‘fUsePattern’];

@table_qualifier-database name

@table_owner is the schema name

@table_type- it takes only 3 arguments and will give ouput based on that TABLE, SYSTEMTABLE, and VIEW

[@fUsePattern- for pattern matching

Ex:

exec sp_tables @Table_Type = ”’VIEW”’

exec sp_tables @Table_Type = ”’TABLE”’

normally we have the used below queries for retruing the  tables information and views information from the database

first and best option is information schema

 

for tables

SELECT [TABLE_NAME]

FROM INFORMATION_SCHEMA.tables

For views

SELECT [TABLE_NAME]

FROM INFORMATION_SCHEMA.views

Alternatively, the INFORMATION_SCHEMA.TABLES system view can also be used to return user views.

SELECT TABLE_NAME

FROM INFORMATION_SCHEMA.TABLES

WHERE TABLE_TYPE = ‘VIEW’

The INFORMATION_SCHEMA.TABLES system view returns both view and user tables.  To return just user views, the TABLE_TYPE needs to be checked for a value of ‘VIEW’.

The third way of getting a list of user views is by querying the different system views, namely, the sys.viewssys.objectsand dbo.sysobjects.

For views:

Select * from sys.views

SELECT [Name] FROM [sys].[objects] WHERE [type] = ‘U’

SELECT [Name] FROM [dbo].[sysobjects] WHERE [xtype] = ‘U’

 

For tables:

select * from sys.tables

SELECT [Name] FROM [sys].[objects] WHERE [type] = ‘U’

SELECT [Name] FROM [dbo].[sysobjects] WHERE [xtype] = ‘U’

Please check and let us know if you have any concerns on this

thanks for reading

adding the error message to the event viewer through t-sql


We have the extended stored procedure in the sql server master database

go to object exploerer-> databases-> system databases-> programmability->extended stored procedures

Extended stored procedures are created and loaded using DLL’s (which are created in C/C++), and offer more independent functionality than standard procedures (such as access to the Win32 API, services, direct disk/network  etc), and because they’re compiled externally as dynamic linking libraries (DLL’s) and these are very faster

sending the error message to the event viewer functionality is supported by one of the extended Procedure  xp_logevent

which is used to send the user defined message to the event viewer through the t-sql

syntax:

xp_logevent {error_number, ‘message’} [, ‘severity’]

The error_number parameter is a user-defined error number greater than 50,000.  The message parameter is the actual message that will be shown in the event viewer. Lastly, severity is an optional parameter containing one of three character strings that represents how bad the error that occurred was. The three possible values are INFORMATIONAL, WARNING, and ERROR. If you don’t include this parameter, the INFORMATIONAL is used.

Example:

Exec master..xp_logevent 65000, “User defined Error message”, WARNING

or

use master

exec xp_logevent 65000, “User defined Error message”, INFORMATIONAL

 

 

Next, run event viewer (Start -> Programs -> Administrative Tools -> Event Viewer) and click on the application log node. Look at the top couple of lines and find an entry with a source of “MSSQLSERVER”; double-click on it. It should load a property page showing our error message, number, and severity, like this:

 

you will see the message .

 

if we double click on these , they are like below

 

 

 

 

 

Please let us know if you have any other options for this..

thanks for viewing

increment the string value letter by letter for every row


Hi ,

This is my first post in this blog.  yesterday my friend  asked  me how to display the sting letter by letter in sql server

we have string function substring used here

below is the script for this

use tempdb

go

if object_id(‘tempdb..#temp’) is not null

begin

drop table #temp

end

go

— declaring the  variable to store the string

declare @test varchar(100)

set @test=’chaitanya’  — assigning the value to the variable

declare @count int

select @count=len(@test)

declare @i int

set @i=1

create table #temp

(

column1 varchar(100)

)

declare @result varchar(100)

declare @j int

set @j=0

while(@i <=@count)

begin

select @result =substring(@test,@j,@i+1)

insert #temp(column1)

select @result

set @i=@i+1

end

select  * from #temp

  –result

c

ch

cha

chai

chait

chaita

chaitan

chaitany

chaitanya

Please let me know if you have any concerns or any suggestions

 

Regards,

Chaitanya

%d bloggers like this: