USE DBNAME
GO
SELECT * FROM Sys.Objects
WHERE Type IN ('U', 'P')
ORDER BY type_desc
'U' denotes UserTables and
'P' denoted Stored Procedures
Display all the Tables and Stored Procedures in a database
Use this query to get all the tables and stored procedures in a database
USE DBNAME
GO
SELECT * FROM Sys.Objects
WHERE Type IN ('U', 'P')
ORDER BY type_desc
'U' denotes UserTables and
'P' denoted Stored Procedures
USE DBNAME
GO
SELECT * FROM Sys.Objects
WHERE Type IN ('U', 'P')
ORDER BY type_desc
'U' denotes UserTables and
'P' denoted Stored Procedures
Determine the current week in a month
It is quiet easy to determine the current week in a year as shown below :
SELECT DATEPART( wk, GETDATE())
However, to determine the current week in a month, use this query written by AMB
select datediff(week, convert(varchar(6), getdate(), 112) + '01', getdate()) + 1
returns 5 as the date today is 24 December 2007
SELECT DATEPART( wk, GETDATE())
However, to determine the current week in a month, use this query written by AMB
select datediff(week, convert(varchar(6), getdate(), 112) + '01', getdate()) + 1
returns 5 as the date today is 24 December 2007
Viewing database information using Sql 2005
A system stored procedure called sp_helpdb allows you to view database information.
The sp_helpdb returns information like the database name, size, owner, ID, date of creation, various database setting and options, compatibility level etc.
To return information about a database :
EXEC sp_helpdb 'Northwind'
GO
If you want to return information about all databases :
EXEC sp_helpdb;
GO
The sp_helpdb returns information like the database name, size, owner, ID, date of creation, various database setting and options, compatibility level etc.
To return information about a database :
EXEC sp_helpdb 'Northwind'
GO
If you want to return information about all databases :
EXEC sp_helpdb;
GO
Retrieving Report Information (SSRS 2005)
Sql Server Reporting Services provides a few global collections that you can use in your reports to retrieve report information. One such collection is Globals
Globals contains global variables which can display information such as the report name, execution time or page number. You can use these variables as expressions in a textbox and place these textboxes in the report footer. Some of the expressions are as follows:
Execution Time :
= "Your report executed at " & Globals.ExecutionTime
Page Number :
="You are viewing page " & Globals.PageNumber & " of " & Globals.TotalPages
Report Path and Name :
=Globals.ReportFolder & Globals.ReportName
Similary you can also explore Globals.ExecutionTime which displays the execution date and time as well as Globals.ReportServerUrl which displays the URL of the SSRS server on which the report is being executed.
Globals contains global variables which can display information such as the report name, execution time or page number. You can use these variables as expressions in a textbox and place these textboxes in the report footer. Some of the expressions are as follows:
Execution Time :
= "Your report executed at " & Globals.ExecutionTime
Page Number :
="You are viewing page " & Globals.PageNumber & " of " & Globals.TotalPages
Report Path and Name :
=Globals.ReportFolder & Globals.ReportName
Similary you can also explore Globals.ExecutionTime which displays the execution date and time as well as Globals.ReportServerUrl which displays the URL of the SSRS server on which the report is being executed.
Save SELECT query output to a text file
Ever needed to save the result of your SELECT query to a text file. Well use xp_cmdshell. xp_cmdshell is an extended stored procedure kept in the master database. It issues OS commands directly to the Windows command shell. You need to be a member of the sys_admin group to use this command or have the xp_sqlagent_proxy_account.
To save your SELECT query results to a text file, use this query :
EXEC master..xp_cmdshell'bcp "SELECT TOP 5 CUSTOMERID FROM Northwind.dbo.Customers" queryout "c:\text.txt" -c -T -x'
One word of caution. xp_cmdshell operates synchronously. So the caller has to wait for the control to be returned to him/her until the command-shell command is completed.
Note: By default, xp_cmdshell is disabled in SQL 2005 for security reasons. To enable it, use the Surface Area Configuration tool or sp_configure. To enable xp_cmdshell using sp_configure, use this query :
EXEC master.dbo.sp_configure 'show advanced options', 1
RECONFIGURE
EXEC master.dbo.sp_configure 'xp_cmdshell', 1
RECONFIGURE
To save your SELECT query results to a text file, use this query :
EXEC master..xp_cmdshell'bcp "SELECT TOP 5 CUSTOMERID FROM Northwind.dbo.Customers" queryout "c:\text.txt" -c -T -x'
One word of caution. xp_cmdshell operates synchronously. So the caller has to wait for the control to be returned to him/her until the command-shell command is completed.
Note: By default, xp_cmdshell is disabled in SQL 2005 for security reasons. To enable it, use the Surface Area Configuration tool or sp_configure. To enable xp_cmdshell using sp_configure, use this query :
EXEC master.dbo.sp_configure 'show advanced options', 1
RECONFIGURE
EXEC master.dbo.sp_configure 'xp_cmdshell', 1
RECONFIGURE
Find out all the Primary Key and Foreign Key Constraints in a table
To find out all the Primary Key and Foreign Key Constraints in a table using Sql Server 2005, use the INFORMATION_SCHEMA views. These views help retrieve the meta data information in an efficient manner.
Here is a query that demonstrated how to retrieve primary and foreign key for all tables in a database:
USE
SELECT Table_Name as [Table], Column_Name as [Column],
Constraint_Name as [Constraint], Table_Schema as [Schema]
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
ORDER BY [Table], [Column]
Here is a query that demonstrated how to retrieve primary and foreign key for all tables in a database:
USE
SELECT Table_Name as [Table], Column_Name as [Column],
Constraint_Name as [Constraint], Table_Schema as [Schema]
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
ORDER BY [Table], [Column]
Generate a unique number in Sql Server 2005
We often find ourselves in the need of generating unique numbers in our database applications.
Let us quickly take a tour of how to do that using Sql server 2005.
SQL Server contains the NEWID() function. This function creates a unique value of type uniqueidentifier. We can use this function in several ways to generate unique numbers to suit our requirements :
Generate GUID's :
UniqueIndentifiers are also knows as GUID's. To generate a GUID use :
SELECT NEWID() as GuidNo
generates FBF2D8E9-F8BE-4F0B-9D49-7CA7C2E3F22C on my machine
Generate only digits :
SELECT ABS(CAST(CAST(NEWID() AS VARBINARY(5)) AS Bigint)) as UniqueNo
generates 427357674589 on my machine
Generate fixed digit unique numbers :
At times, we may also need to generate fixed digit numbers. You can do that in the following manner :
SELECT REPLACE(STR(CAST(CAST(NEWID() AS binary(5)) AS bigint),12),0,0)
generates 470370453006 on my machine
Let us quickly take a tour of how to do that using Sql server 2005.
SQL Server contains the NEWID() function. This function creates a unique value of type uniqueidentifier. We can use this function in several ways to generate unique numbers to suit our requirements :
Generate GUID's :
UniqueIndentifiers are also knows as GUID's. To generate a GUID use :
SELECT NEWID() as GuidNo
generates FBF2D8E9-F8BE-4F0B-9D49-7CA7C2E3F22C on my machine
Generate only digits :
SELECT ABS(CAST(CAST(NEWID() AS VARBINARY(5)) AS Bigint)) as UniqueNo
generates 427357674589 on my machine
Generate fixed digit unique numbers :
At times, we may also need to generate fixed digit numbers. You can do that in the following manner :
SELECT REPLACE(STR(CAST(CAST(NEWID() AS binary(5)) AS bigint),12),0,0)
generates 470370453006 on my machine
Check if a database or table exists using Sql Server 2005
The Object_ID() function in SQL Server can be utilised in a number of ways. One such utility is to verify if an object exists.
The Object_ID() takes in the object name and object type as parameters. The object name is the object used and the object type is the type of object used in a schema.
For example to check if a table exists in a database, use this query :
IF OBJECT_ID ('AdventureWorks.dbo.AWBuildVersion','U') IS NOT NULL
Print 'Table Exists'
ELSE
Print 'Table Does Not Exists'
where 'AdventureWorks.dbo.AWBuildVersion' is the object name and 'U' is the object type which represents a table
Similarly you can check for a stored procedure or a view by specifying the correct object type. You can get an entire list of object types over here.
To check if a database exists, you can use the DB_ID() function as shown below :
IF db_id('AdventureWorks') IS NOT NULL
Print 'Database Exists'
ELSE
Print 'Database Does Not Exists'
The Object_ID() takes in the object name and object type as parameters. The object name is the object used and the object type is the type of object used in a schema.
For example to check if a table exists in a database, use this query :
IF OBJECT_ID ('AdventureWorks.dbo.AWBuildVersion','U') IS NOT NULL
Print 'Table Exists'
ELSE
Print 'Table Does Not Exists'
where 'AdventureWorks.dbo.AWBuildVersion' is the object name and 'U' is the object type which represents a table
Similarly you can check for a stored procedure or a view by specifying the correct object type. You can get an entire list of object types over here.
To check if a database exists, you can use the DB_ID() function as shown below :
IF db_id('AdventureWorks') IS NOT NULL
Print 'Database Exists'
ELSE
Print 'Database Does Not Exists'
Return date without time using Sql 2005
By default, you get both the date and time value whenever you retrieve a datetime value from the database. So something like :
SELECT GETDATE()
returns 2007-12-17 21:17:33.933
Let us quickly see how to return only the date portion in different formats
YYYY-MM-DD
SELECT CONVERT(VARCHAR(10), GETDATE(), 120)
returns 2007-12-17
MM-DD-YYYY
SELECT CONVERT(VARCHAR(10), GETDATE(), 110)
returns 12-17-2007
DD-MM-YYYY
SELECT CONVERT(VARCHAR(10), GETDATE(), 105)
returns 17-12-2007
SELECT GETDATE()
returns 2007-12-17 21:17:33.933
Let us quickly see how to return only the date portion in different formats
YYYY-MM-DD
SELECT CONVERT(VARCHAR(10), GETDATE(), 120)
returns 2007-12-17
MM-DD-YYYY
SELECT CONVERT(VARCHAR(10), GETDATE(), 110)
returns 12-17-2007
DD-MM-YYYY
SELECT CONVERT(VARCHAR(10), GETDATE(), 105)
returns 17-12-2007
Configure a Report to be cached (SSRS 2005)
If you have reports which are large in size, accessed frequently and do not display recent data, then the reports qualify to be cached. A cached report is a copy of the processed report which is returned to the user everytime, till the cached copy gets expired. With reports containing parameters, multiple version of the report are cached based on the parameters
Step 2: Navigate to the report for which you want to configure the cache options; in our case we will navigate to the Customer report as shown below.
To configure a cache, follow these steps :
Step 1: Navigate to the Report Manager (http://localhost/Reports/Pages/) and click on the Contents tab. Here you will see a list of reports installed. For this example, I will use the Northwind reports as shown below. You can use any of the reports you desire to cache.
Step 2: Navigate to the report for which you want to configure the cache options; in our case we will navigate to the Customer report as shown below.
Step 3: Click on the Properties > Execution tab. You will see some options for caching the report as shown below
Step 4: Click on "Cache a temporary copy of the report. Expire copy of report after a number of minutes". Set the minutes to 30.
I would also encourage you to try out the other options on the form.
To read more about caching report, follow this link.
To read more about caching report, follow this link.
Difference between two dates
One of the most common requirement encountered is to find the difference between two dates. In Sql 2005, we can achieve this requirement easily by using the DATEDIFF() function.
DATEDIFF() returns a value in datepart format, specifying the difference between two specified dates.
Syntax : DATEDIFF ( datepart , startdate , enddate )
Eg:
SELECT OrderID,CustomerID,OrderDate,
DATEDIFF(yy, OrderDate, GETDATE()) YearsPassedSinceOrder
FROM Northwind.dbo.Orders
returns the no. of years passed since the order was placed.
Similarly to find number of months, use 'm' as the datepart or 'd' to determine no. of days.
You can find other values for datepart over here
DATEDIFF() returns a value in datepart format, specifying the difference between two specified dates.
Syntax : DATEDIFF ( datepart , startdate , enddate )
Eg:
SELECT OrderID,CustomerID,OrderDate,
DATEDIFF(yy, OrderDate, GETDATE()) YearsPassedSinceOrder
FROM Northwind.dbo.Orders
returns the no. of years passed since the order was placed.
Similarly to find number of months, use 'm' as the datepart or 'd' to determine no. of days.
You can find other values for datepart over here
Write For Us
The SQL Server Curry team would like to Thank You for showing your interest in contributing to the blog. You as a sqlservercurry reader are an integral part of the sqlservercurry community. Your blog post will be read by thousands of similar developers like you, who would give you valuable feedback on the same.
To make sure, that we are consistent in maintaining the quality of content we publish, there are a few guidelines that we would request you to read.
Guidelines
As an author, kindly read the following guidelines.
1. The contents of the blog post needs to be your original work and should not be borrowed from a site/person. In case you are adding some source code that has been posted by others, please take their permission and kindly mention the name/source clearly.
2. All posts needs to be published in a word document at writeforus[attherate]sqlservercurry.com (replace [attherate] with @).
3. The language of the posts need to be simple and free of grammatical and other errors. If you are good at SQL Server, but would like some assistance in writing and editing your stuff, kindly contact suprotimagarwal[attherate]sqlservercurry.com directly. He would be glad to help you out.
4. Include all the code, tables, figures, screen shots etc along with the post. Place the images at the appropriate positions in the word document. We would appreciate if you could send us the images seperately along with the word document.
5. Mention your name, email address and a short description about yourself in the Article. This will help us give you credit for your piece of work.
What happens after we have received an article?
Once you have submitted an article, we will review your article, checking the content for accuracy. This process can take 1-3 days. Once the editorial process is complete, we will publish your article on our website.
Where do I send my article?
You can mail us your article at writeforus[attherate]sqlservercurry.com
To make sure, that we are consistent in maintaining the quality of content we publish, there are a few guidelines that we would request you to read.
Guidelines
As an author, kindly read the following guidelines.
1. The contents of the blog post needs to be your original work and should not be borrowed from a site/person. In case you are adding some source code that has been posted by others, please take their permission and kindly mention the name/source clearly.
2. All posts needs to be published in a word document at writeforus[attherate]sqlservercurry.com (replace [attherate] with @).
3. The language of the posts need to be simple and free of grammatical and other errors. If you are good at SQL Server, but would like some assistance in writing and editing your stuff, kindly contact suprotimagarwal[attherate]sqlservercurry.com directly. He would be glad to help you out.
4. Include all the code, tables, figures, screen shots etc along with the post. Place the images at the appropriate positions in the word document. We would appreciate if you could send us the images seperately along with the word document.
5. Mention your name, email address and a short description about yourself in the Article. This will help us give you credit for your piece of work.
What happens after we have received an article?
Once you have submitted an article, we will review your article, checking the content for accuracy. This process can take 1-3 days. Once the editorial process is complete, we will publish your article on our website.
Where do I send my article?
You can mail us your article at writeforus[attherate]sqlservercurry.com
Advertise
To advertise on this site, kindly email us at business[attherate]a2zknowledgevisuals[dot]com or business[attherate]dotnetcurry[dot]com
About Us
Suprotim Agarwal, ASP.NET Architecture MVP, MCSD, MCAD, MCDBA, MCSE, is the CEO of A2Z Knowledge Visuals Pvt. He primarily works as an Architect Consultant and provides consultancy on how to design and develop .NET centric database solutions. Suprotim also holds a Masters in Computer Management. He has been working with .NET technologies ever since its 1.0 beta release, way back in 2000.
Suprotim is the founder and primary contributor to DotNetCurry, SQLServerCurry and DevCurry. He has also written the first eBook on jQuery and ASP.NET called 51 Recipes on using jQuery with ASP.NET Controls
Suprotim is also a 'Moderator MVP' on the MSDN Forums and a Top Answerer in the ASP.NET forums. Follow him on twitter @SuprotimAgarwal . You can contact him via email at suprotimagarwal[attherate]dotnetcurry.com[donotspam]
Gauri Sohoni is a freelance consultant and trainer. With over 12 years of experience and handling multiple profiles as a developer as well as trainer, Gauri has now focused her activities on SQL Server. Gauri also hold numerous Microsoft Certifications and has recently cleared her certification in SQL Server 2005.
Madhivanan is a SQL Server MVP and an MSc computer Science graduate from Chennai, India and works as a Lead Subject Matter Expert at a company that simplifies BIG data. Madhivanan was basically a developer who started with Visual Basic 6.0, SQL Server 2000 and Crystal Report 8. As years passed by, he started working more on writing queries in SQL Server in most of the projects developed in his company. He has some good level of experience in ORACLE, MySQL and PostgreSQL as well. He is one of the leading contributors at www.sqlteam.com and also a moderator at www.sql-server-performance.com. He also blogs at http://beyondrelational.com/blogs/madhivanan
Suprotim is the founder and primary contributor to DotNetCurry, SQLServerCurry and DevCurry. He has also written the first eBook on jQuery and ASP.NET called 51 Recipes on using jQuery with ASP.NET Controls
Suprotim is also a 'Moderator MVP' on the MSDN Forums and a Top Answerer in the ASP.NET forums. Follow him on twitter @SuprotimAgarwal . You can contact him via email at suprotimagarwal[attherate]dotnetcurry.com[donotspam]
Gauri Sohoni is a freelance consultant and trainer. With over 12 years of experience and handling multiple profiles as a developer as well as trainer, Gauri has now focused her activities on SQL Server. Gauri also hold numerous Microsoft Certifications and has recently cleared her certification in SQL Server 2005.
Madhivanan is a SQL Server MVP and an MSc computer Science graduate from Chennai, India and works as a Lead Subject Matter Expert at a company that simplifies BIG data. Madhivanan was basically a developer who started with Visual Basic 6.0, SQL Server 2000 and Crystal Report 8. As years passed by, he started working more on writing queries in SQL Server in most of the projects developed in his company. He has some good level of experience in ORACLE, MySQL and PostgreSQL as well. He is one of the leading contributors at www.sqlteam.com and also a moderator at www.sql-server-performance.com. He also blogs at http://beyondrelational.com/blogs/madhivanan
Subscribe to:
Posts (Atom)