Stored procedure which were created in last 7days

Following script will provide name of all the stored procedure which were created in last 7 days, they may or may not be modified after that.

SELECT name
FROM sys.objects
WHERE type = 'P'
AND DATEDIFF(D,modify_date, GETDATE()) < 7

To get the row count in a faster way

To get the row count in a faster way , the conventional way would be very slow.

SELECT rows FROM sysindexes
WHERE id = OBJECT_ID('TABLEA') AND indid < 2

TABLEA : Is the name of the table

Unmatching records between two tables

SELECT SID FROM TABLEA T1 WHERE NOT EXISTS (
SELECT * FROM TABLEB T2
WHERE (T1.SID=T2.SID))

(or)

(select TABLEA.SID from TABLEA) EXCEPT
(select TABLEB.SID from TABLEB)

Exporting the output into a text or csv file

Syntax

C:\>sqlcmd -S \\Server_pathname -i C:\script.sql -o C:\test.txt

Here Script.sql is a sql script in which the desired script is written and the related output is exported into test.txt file at the following location.

Taking a backup or creating a duplicate table

Taking a backup of a table

Here it creates the table name TEST1 from TEST and copies the content accordingly

SELECT SID,CLASS,DEPT,DATE,SALARY,NAME INTO TEST1
FROM TEST


Here it copies the whole table by creating a table name along with the field names

SELECT * FROM TEST1

or

SELECT * INTO TEST1 FROM TEST

Doing a bulk insert of a text file in temp table

SELECT BulkColumn
FROM OPENROWSET (BULK '\\servername.path\test\flatfile.txt',
SINGLE_CLOB)
MyFile

When used with the BULK provider keyword you can name a data file to read as one of three types of objects:

SINGLE_BLOB, which reads a file as varbinary(max)
SINGLE_CLOB, which reads a file as varchar(max)
SINGLE_NCLOB, which reads a file as nvarchar(max)

Getting a SQL Server RowCount Without doing a Table Scan

Sometimes there is a need to get record counts from every table in your database. One way of doing this is to do a SELECT count(*) on all of your tables, but this could create a lot of overhead especially for large databases and large tables. If you don't require an exact answer, it isn't necessary to use a SELECT count(*) query on the rows in a table to get the row count.

SELECT so.[name] as [table name]
, CASE WHEN si.indid between 1 and 254
THEN si.[name] ELSE NULL END
AS [Index Name]
, si.indid
FROM sysindexes si
INNER JOIN sysobjects so
ON si.id = so.id
WHERE si.indid <>
AND so.type = 'U' -- Only User Tables
AND so.[name] != 'dtproperties'
ORDER BY so.[name]

How to get length of data in Text, NText and Image columns in SQL Server

There is sometimes a need to figure out the maximum space that is being used by a particular column in your database. You would initially think that the LEN() function would allow you to do this, but this function does not work on Text, NText or Image data types, so how do you figure out the length of a value in a column that has one of these data types?

SELECT name, DATALENGTH(packagedata) FROM dbo.sysdtspackages