Order by in functions

their is a rule which i got to know before few days and i want to share with u that you cant use order by in functions....when u try to do so u will get this error..


sg 1033, Level 15, State 1, Procedure GetDataBaseDetail, Line 21
The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified.

so to solve this problem you will have to use 'top ' ex.- top 1000 . with query where u want to use order by cluse.

Read More

Search metter in complete database

you can use this procedure to search a value in complete database ..

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- this procedure is created to search any value in all table of currently opened database
ALTER PROC [dbo].[SearchAllTables]
(
@SearchStr nvarchar(100)
)
AS
BEGIN



CREATE TABLE #Results (ColumnName nvarchar(370), ColumnValue nvarchar(3630))

SET NOCOUNT ON

DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110)
SET @TableName = ''
SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')

WHILE @TableName IS NOT NULL
BEGIN
SET @ColumnName = ''
SET @TableName =
(
SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
AND QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
AND OBJECTPROPERTY(
OBJECT_ID(
QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
), 'IsMSShipped'
) = 0
)

WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)
BEGIN
SET @ColumnName =
(
SELECT MIN(QUOTENAME(COLUMN_NAME))
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = PARSENAME(@TableName, 2)
AND TABLE_NAME = PARSENAME(@TableName, 1)
AND DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar')
AND QUOTENAME(COLUMN_NAME) > @ColumnName
)

IF @ColumnName IS NOT NULL
BEGIN
INSERT INTO #Results
EXEC
(
'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630)
FROM ' + @TableName + ' (NOLOCK) ' +
' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
)
END
END
END

SELECT ColumnName, ColumnValue FROM #Results
END

Read More

search name of table by column name

some times this problem occurs when ur database is too large and u know the name of column but u dont know the name of table it belongs from..so to search the name of table which contain that column, u can use this procedure.....

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create procedure [dbo].[GetTableOfColumn]
--this procedure is to search a table name of column in a database by column name
@ColVar varchar(30) =''
as
begin
Set NoCount On



Select

Convert(Char(75), SysObjects.Name) 'Table Names:',

Convert(Char(75), SysColumns.Name) 'Column Names:'



From SysObjects, SysColumns, SysTypes



Where SysObjects.ID = SysColumns.ID

And SysColumns.xType = SysTypes.xType

And SysColumns.Name like @ColVar



Order by SysObjects.Name Asc



Set NoCount Off

end

Read More

Auto Documentation of database

Now u need not to write documentation in notebooks or by typeing all the tables names and fields names and their details in ms word...just by this single functiion u can easly get complete documentation of your current opened database...


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create function [dbo].[GetDataBaseDetail]() returns table
as
return(
SELECT top 10000 c.TABLE_NAME 'Table Name',c.column_name 'Column Name',
data_type 'Data Type',
CHARacter_maximum_length 'Maximum Length',
IS_NULLABLE 'Allow Null',
tc.CONSTRAINT_NAME 'Constraint Name'
,s.value 'Description'
FROM information_schema.columns c
left join INFORMATION_SCHEMA.KEY_COLUMN_USAGE tc on c.COLUMN_NAME = tc.COLUMN_NAME
left join sys.extended_properties s on s.major_id = OBJECT_ID(c.TABLE_SCHEMA+'.'+c.TABLE_NAME)
AND s.minor_id = c.ORDINAL_POSITION
AND s.name = 'MS_Description'
inner join INFORMATION_SCHEMA.TABLES tbl
on tbl.TABLE_NAME = c.TABLE_NAME
and tbl.TABLE_TYPE = 'BASE TABLE'

group by c.TABLE_NAME ,tc.CONSTRAINT_NAME,c.COLUMN_NAME,DATA_TYPE,CHARACTER_MAXIMUM_LENGTH ,IS_NULLABLE,s.value
order by c.TABLE_NAME ,tc.CONSTRAINT_NAME desc
)

Read More