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.
Order by in functions
Labels: SQL
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.
Labels: SQL
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
Labels: SQL
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
Labels: SQL
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
Labels: SQL
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
Labels: SQL
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
)
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
)