List All The Column With Specific Data Types of Specific Table

Since we upgraded to SQL Server 2005 from SQL Server 2000, we have used following script to find out columns with specific datatypes many times. It is very handy small script.


select c.name as ColumnName,
(select name from sys.types where user_type_id=c.user_type_id)as Type,
c.max_length as Size
from sys.columns c where object_id=
(select object_id from sys.objects where name='order' and type_desc='USER_TABLE')

Database Backup by Stored Procedure

---------------
--Backup all db
---------------
DECLARE @name VARCHAR(50)
DECLARE @path VARCHAR(256)
DECLARE @fileName VARCHAR(256)
DECLARE @fileDate VARCHAR(20)

SET @path = 'C:\backup\DB\' ---Give Your Path Here where you want to store this backup file
SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),105) -- Add Date in File Name
DECLARE db_cursor CURSOR FOR
SELECT name
FROM master.dbo.sysdatabases
where name = 'Kool_Project_Test' ---Name of Database that you want to get Backup

OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @name

WHILE @@FETCH_STATUS = 0
BEGIN
SET @fileName = @path + @name +'_'+ @fileDate + '.BAK'
BACKUP DATABASE @name TO DISK = @fileName WITH NOFORMAT, INIT

FETCH NEXT FROM db_cursor INTO @name
END

CLOSE db_cursor
DEALLOCATE db_cursor

Find Duplicate Rows in a Sql Table

select SHORT_NAME, count(SHORT_NAME) as SN
from TBLKOOL_GENERAL
group by SHORT_NAME

Sql Qurey to Select Multiple Tabels and Procedure

select * from sys.tables Where name like 'tblKool%'
select * from sys.procedures Where name like 'spKool%' order by name
BEGIN
FOR r in (SELECT object_name FROM user_objects WHERE object_type = 'PROCEDURE' )
LOOP
EXECUTE IMMEDIATE 'DROP PROCEDURE 'r.object_name;
END LOOP
END

Query to get all column names from database table in SQL Server.

SELECT COLUMN_NAME,Data_Type+'('+convert(varchar,character_maximum_length)+')'as DataType

FROM INFORMATION_SCHEMA.COLUMNS

WHERE TABLE_NAME = 'Your Table Name'

ORDER BY ORDINAL_POSITION